How to obtain size of all tables in SQL server??

There is a stored procedure to retrieve the information on the creation and modification dates and on the size usage of the every table of a database. This takes the input as the table name.

The syntax for this as below,

EXEC sp_spaceused “db_table_name”

But to revieve the meta data information of every table of a single database we can make use of the iteration procedure ‘sp_msForEachTable’.

And so we can give the query as below which gives the metadata for every table of the selected database but as multiple table results and not as a single view.

EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?””
To achieve the result as a single view we can follow the following steps.

First create a temporary table,

CREATE TABLE temp_table
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

Then insert the result of the above defined procedures,

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?””

Now we can view the result of all the tables in a single view as and can drop the table after use to avoid use of disk space,

Select * from temp_table
DROP TABLE temp_table

So we can have the required result of all the meta details of the single database as a single shot view. Thanks for reading this post. Feel free to share your views and comments.