[SQL Server] Display Table size details

[SQL Server] Display Table size details

Script

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

SELECT SCHEMA_NAME(tbl.schema_id) AS [Schema], tbl.name, COALESCE( ( SELECT pr.name FROM sys.database_principals pr WITH(NOLOCK) WHERE pr.principal_id = tbl.principal_id ), SCHEMA_NAME(tbl.schema_id)) AS [Owner], tbl.max_column_id_used AS [Columns], CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS BIT) AS [HasClusIdx], COALESCE( ( SELECT SUM(spart.ROWS) FROM sys.partitions spart WITH(NOLOCK) WHERE spart.object_id = tbl.object_id AND spart.index_id < 2 ), 0) AS [RowCount], COALESCE( ( SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(a.used_pages - CASE WHEN a.TYPE <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.indexes AS i WITH(NOLOCK) JOIN sys.partitions AS p WITH(NOLOCK) ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.allocation_units AS a WITH(NOLOCK) ON a.container_id = p.partition_id WHERE i.object_id = tbl.object_id ), 0.0) / 1024 AS [IndexMB], COALESCE( ( SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(CASE WHEN a.TYPE <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.indexes AS i WITH(NOLOCK) JOIN sys.partitions AS p WITH(NOLOCK) ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.allocation_units AS a WITH(NOLOCK) ON a.container_id = p.partition_id WHERE i.object_id = tbl.object_id ), 0.0) / 1024 AS [DataMB], tbl.create_date, tbl.modify_date FROM sys.tables AS tbl WITH(NOLOCK) INNER JOIN sys.indexes AS idx WITH(NOLOCK) ON(idx.object_id = tbl.object_id AND idx.index_id < 2) INNER JOIN MASTER.dbo.spt_values v WITH(NOLOCK) ON(v.NUMBER = 1 AND v.TYPE = 'E') --WHERE tbl.Name like '%tablename%' ORDER BY DataMB DESC;

Result example