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.

SQL Query to retrieve table size (SQL Server)

Hello all, its been months since i have written a blog. Its never late than never. So let me start it right away with a post on SQL. This is my first post on SQL and hope you all find it useful.

I am today giving a post on getting the size of the tables in your database. Its very simple and we have a system defined procedure that makes your job easier. The syntax to get this is,

exec sp_spaceused ‘TABLE NAME’

You shall get the metdata information on your table as you query the above.

Lets try to get the size information on all the tables of your whole DB. Wondering how? This is even simpler, as usual to make the job easy we have got a time saving procedure to do it for us. Lets make a query to get the size of all tables,

EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

The question mark replaces the table names of our DB. This query returns mutiple tables of the metadata like size of every table in our database. To get a view of the metadata as a single table the following set of query can be run together,

/* CREATE A TABLE TO STORE THE INFORMATION */

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

/*INSERTING THE INFORMATION RETRIEVED TO THE TABLE*/

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

/*VIEW THE INFORMATION FROM THE TABLE & DROPPING THE TABLE*/

SELECT * FROM #t

DROP TABLE #t /*OPTIONAL TO DROP*/

Hope this post was useful to you all. Thanks and feel free to comment and share your ideas.