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’.

CREATE TABLE #tbl_SPACETABLE(NAME VARCHAR(100),ROWS INT,RESERVED VARCHAR(100),DATA VARCHAR(100),INDEXSIZE VARCHAR(100),UNSUSED VARCHAR(100))

Insert the stored procedure ‘SP_MSFOREACHTABLE ‘ results by using below command.

INSERT INTO #tbl_SPACETABLE EXEC SP_MSFOREACHTABLE @COMMAND1="EXEC SP_SPACEUSED '?' "

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

a database.

? 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

SELECT * FROM #tbl_SPACETABLE

If you need to fetch space for single table,then use below query.

SP_SPACEUSED 'TABLE_NAME'

Hope you will find the space of all tables easily.

---