Get all user defined table information with RowCount and Size (space used)

"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 the updated details.

 

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

 

 

- Mohit Nayyar