Find the Size of all Tables in a Database – SQL Server Query

Hi All,

Find the Size of all Tables in a Database is a simple and most common requirement. And whenever I come across these requirements, I get a little nervous since I am not very good in coding/programming. I wonder how these Poker games online like http://de.partypoker.com/ program the entire thing. Anyways, back to the solution:

You might now there is s stored procedure called sp_spaceused which gives all details regarding the size of the table:

USE AdventureWorks
GO
 
EXEC sp_spaceused 'sales.salesorderheader'

The output is as follows:

1_SQL_Server_Query_to_find_the_size_of_all_tables_in_a_database

All we need to do is: create a temporary table to record the above data items, run the code in a loop for all the tables, and record in the temp table. Here is the solution:

-- craete the temporary table
CREATE TABLE #tableSize
([tablename] NVARCHAR(128),
 [noofrows] CHAR(18),
 sizereserved VARCHAR(20),
 sizedata VARCHAR(20),
 index_size VARCHAR(20),
 unused VARCHAR(20)
 )
 
--insert the data
INSERT #tableSize EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

--select & drop the table
SELECT *FROM #tableSize order by tablename
DROP TABLE #tableSize

And you get the following output:

2_SQL_Server_Query_to_find_the_size_of_all_tables_in_a_database

HTH !

 

Regards

Rahul Sharma

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *