Sometime there is simple solution to a complex problem.
Few days ago, I have to find each database table sizes (disk space used by every table) and number of rows for database optimization purpose. When we think about such kind of problem, we may think that could be difficult to find disk space used by every table, but we can easily count number of rows in every table of a database by getting database tables from master database. But how to find disk space used by tables? Question is very complex, but the answer is very simple.
We can use following query to get rows count and disk space used by each database table. This query will return disk space in KB, shows result in a second and It’s neither lock any table nor disturb your production work.
t.NAME AS TableName,
s.Name AS SchemaName,
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
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
t.Name, s.Name, p.Rows