Thursday, 5 November 2015

Get all the table size in a SQL Server Database with the help of T-SQL

With the help of the T-SQL query below we can measure the size of all the tables exists under a SQL Server Database

create table #Size (
    Name varchar(255),
    [rows] int,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255))
create table #TableSize (
    TableName varchar(255),
    NoOfRows int,
    ReservedSizeMB int,
    DatSizeMB int,
    ReservedIndexSizeMB int,
    UnusedSizeMB int)

EXEC sp_MSforeachtable @command1="insert into #Size
EXEC sp_spaceused '?'"
insert into #TableSize (TableName, NoOfRows, ReservedSizeMB, DatSizeMB, ReservedIndexSizeMB, UnusedSizeMB)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2)/1024,
SUBSTRING(data, 0, LEN(data)-2)/1024,
SUBSTRING(index_size, 0, LEN(index_size)-2)/1024,
SUBSTRING(unused, 0, LEN(unused)-2)/1024
from #Size

select *,TotalSpaceGB=(DatSizeMB+ReservedIndexSizeMB)/1024 from #TableSize
order by TableName

drop table #Size
drop table #TableSize

No comments:

Post a comment