|
|
|
Title:
|
SQL - Table Sizes
|
Language:
|
SQL
|
|
Description:
|
prints out the sizes of tables (based on sysindexes) in a database
|
Views:
|
107
|
|
Author:
|
Joel Meikle
|
Date Added:
|
10/7/2008
|
Copy
|
Code
|
|
SELECT LEFT(OBJECT_NAME(id), 30) AS [Table],
CAST(CAST(reserved * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS 'Allocated (in M)',
CAST(CAST(dpages * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS 'Used (in M)',
CAST(CAST((reserved - dpages) * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS 'Unused (in M)',
rowcnt AS 'Row Count (approx.)'
FROM sysindexes
WHERE OBJECT_NAME(id) NOT LIKE 'sys%' AND OBJECT_NAME(id) NOT LIKE 'dt%'
ORDER BY reserved DESC, LEFT(OBJECT_NAME(id), 30)
-- OR summed as the above has some duplicates
SELECT LEFT(OBJECT_NAME(id), 30) AS [Table], SUM(CAST(CAST(reserved * 8192 AS DECIMAL(10, 1)) / 1000000.0 AS DECIMAL(10, 1))) AS [Allocated (in M)],
SUM(CAST(CAST(dpages * 8192 AS DECIMAL(10, 1)) / 1000000.0 AS DECIMAL(10, 1))) AS [Used (in M)], SUM(CAST(CAST((reserved - dpages) * 8192 AS DECIMAL(10, 1))
/ 1000000.0 AS DECIMAL(10, 1))) AS [Unused (in M)], SUM(rowcnt) AS [Row Count (approx.)]
FROM sys.sysindexes
WHERE (NOT (OBJECT_NAME(id) LIKE 'sys%')) AND (NOT (OBJECT_NAME(id) LIKE 'dt%'))
GROUP BY LEFT(OBJECT_NAME(id), 30), reserved
ORDER BY reserved DESC, [Table]
|
|
Usage
|
run in sql management studio or query analizer
|
|
Notes
|
|
you may need to alter the where if you have table names starting with "sys" or "dt"
|
|
|