Determine the size of database tables in a MSSQL Server Database.
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
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