Determining the Space Used by all tables in a Database?
Determining the Space Used by all tables in a Database
Lets create one temporary table to store the output of stored procedure ‘SP_MSFOREACHTABLE’.
Insert the stored procedure ‘SP_MSFOREACHTABLE ‘ results by using below command.
It will insert below columns in the table #tbl_SPACETABLE.
|name||nvarchar(128)|| Name of the object for which space usage information was requested.|
The schema name of the object is not returned. If the schema name is required, use the sys.dm_db_partition_stats or sys.dm_db_index_physical_stats dynamic management views to obtain equivalent size information.
|rows||char(11)||Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.|
|reserved||varchar(18)||Total amount of reserved space for objname.|
|data||varchar(18)||Total amount of space used by data in objname.|
|index_size||varchar(18)||Total amount of space used by indexes in objname.|
|unused||varchar(18)||Total amount of space reserved for objname but no yet used.|
SP_SPACEUSED stored procedure is used to find the space used by objects in
? is passed as argument for sp_spaceused stored procedure to retrieve space for all tables in a database.
Fetch the space used by all tables in a database by runnning below query
If you need to fetch space for single table,then use below query.
Hope you will find the space of all tables easily.