Friday, April 10, 2015

Table Space used and row count



Using below query you can find out the table space in a database and rows count of the table. 

Please change/add your tables list in WHERE condition  


SELECT T.NAME AS TableName, P.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB,  SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 
 FROM  sys.tables T
 INNER JOIN      
    sys.indexes I ON t.OBJECT_ID = i.object_id
 INNER JOIN 
    sys.partitions P ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
 INNER JOIN 
    sys.allocation_units A ON p.partition_id = a.container_id
 LEFT OUTER JOIN 
    sys.schemas S ON t.schema_id = s.schema_id
 WHERE 
    T.NAME in ('Table1','Table2','Table3','Table4') 
    AND T.is_ms_shipped = 0
    AND I.OBJECT_ID > 255 
 GROUP BY 
    T.Name, S.Name, P.Rows
 ORDER BY 
    T.Name

 you can find using SSMS as shown in below. Right click on the database and click on the Reports --> select Standard Reports --> and select Disk Usage by Table. 






and results shown like below