Determine the size of database tables in a MSSQL Server Database.
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 | SELECT DB_NAME() AS DatabaseNase , GETDATE() AS DateCreated , TableName, Rows , Reserved , Data , Used - Data AS IndexSize , Reserved - Used AS Unused FROM ( SELECT USR.name + '.' + OBJ.name AS TableName , MIN(row_count) AS Rows , SUM(8 * reserved_page_count) + MAX(COALESCE(LOBDATA.LobReserved,0)) AS reserved, SUM (8* CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END ) AS Data, SUM (8*used_page_count)+MAX(COALESCE(LOBDATA.LobUsed,0)) AS Used FROM dbo.sysobjects AS OBJ INNER JOIN sys.schemas AS USR ON OBJ.uid = USR.schema_id INNER JOIN sys.dm_db_partition_stats AS PS ON PS.object_id = OBJ.id LEFT JOIN( SELECT parent_id, SUM(8*reserved_page_count) AS LOBReserved, SUM(8*used_page_count) AS LOBUsed FROM sys.dm_db_partition_stats p INNER JOIN sys.internal_tables it ON p.object_id = it.object_id WHERE it.internal_type IN (202,204) GROUP BY IT.parent_id ) AS LOBDATA ON LOBDATA.parent_id = OBJ.Id WHERE OBJ.type='U' GROUP BY USR.name + '.' + OBJ.name ) AS DT order by Data desc |