Tables details (Row count, Column count, Data size)

USE <DatabaseName>
GO
CREATE TABLE #TEMP (TABLE_NAME sysname ,
                    ROW_COUNT INT, reserved_size VARCHAR(50),
                    data_size VARCHAR(50),
                    index_size VARCHAR(50),
                    unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #TEMP EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.TABLE_NAME,
         a.ROW_COUNT,
           COUNT(*) AS col_count,
           a.data_size
FROM #TEMP a
INNER JOIN information_schema.columns b ON 
a.TABLE_NAME COLLATE database_default = b.TABLE_NAME 
COLLATE database_default
GROUP BY a.TABLE_NAME,
           a.ROW_COUNT,
             a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #TEMP

This site uses Akismet to reduce spam. Learn how your comment data is processed.