Technical Article

Get all user def table info

,

"sp_spaceused" procedure is really handy to get the updated information about space usage by database or tables.

I combined the same with another very useful procedure "sp_MSforeachtable" to get this information for all user-defined tables.

If you see any problem with the information then just change the last parameter and set it to TRUE (FALSE in the script). That will update the space usage and then report updated details.

Thanks

Mohit Nayyar

http://mohitnayyar.blogspot.com

CREATE TABLE #temp (TableName NVARCHAR (128), RowsCnt VARCHAR (11), ReservedSpace VARCHAR(18), DataSpace VARCHAR(18), CombinedIndexSpace VARCHAR(18), UnusedSpace VARCHAR(18))
EXEC sp_MSforeachtable 'INSERT INTO #temp (TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace) EXEC sp_spaceused ''?'', FALSE'
SELECT TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace 
FROM #temp
ORDER BY TableName
DROP TABLE #temp

Rate

4.29 (14)

You rated this post out of 5. Change rating

Share

Share

Rate

4.29 (14)

You rated this post out of 5. Change rating