Archive for the ‘sp_MSforeachtable’ Category

How to query each of the table rowcount, reserved space in MB, data space in MB, index size in MB and unused space in MB?

You wanted to find out the row count, space used in MB, index size in MB, and unused space in MB for all tables in the database. With the undocumented stored proc sp_MSforeachtable combining with the sp_spaceused stored proc, now you can and here’s an example of how it can be done:


-- Querying the table rowcount, reserved space in MB, data space in MB, index size in MB, and unused space in MB

-- create a temp table to store the tables spaces info for all tables for all databases in the instance
IF OBJECT_ID('TEMPDB..#TableSpecs') IS NOT NULL
DROP TABLE #TableSpecs

CREATE TABLE #TableSpecs
(
 ID int identity(1,1)
 ,Table_Name VARCHAR(123)
 ,Table_Rowcnt INT
 ,ReservedSpaceKB VARCHAR(25)
 ,SpaceUsedKB VARCHAR(25)
 ,Index_SizeKB VARCHAR(25)
 ,UnusedSpaceKB VARCHAR(25)
)

-- use the sp_msforeachtable stored proc to load the table specs info
INSERT INTO #TableSpecs
EXEC sp_MSforeachtable 'sp_spaceused ''?'''

-- convert spaces from KB to MB
SELECT ts.Table_Name
 ,Table_Rowcnt
 ,ROUND(CAST(REPLACE(ReservedSpaceKB, 'KB', '') AS FLOAT) / 1024,2) AS ReservedSpaceMB
 ,ROUND(CAST(REPLACE(SpaceUsedKB, 'KB', '') AS FLOAT) / 1204,2) AS SpaceUsedMB
 ,ROUND(CAST(REPLACE(Index_SizeKB, 'KB', '') AS FLOAT) / 1024,2) AS IndexSizeMB
 ,ROUND(CAST(REPLACE(UnusedSpaceKB, 'KB', '') AS FLOAT) / 1024,2) AS UnusedSpaceMB
FROM #TableSpecs ts
ORDER BY SpaceUsedMB desc, Table_Rowcnt desc
-- you can change the order by columns as per your desire

References:

sp_spaceused
http://msdn.microsoft.com/en-us/library/ms188776(v=sql.105).aspx