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:

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:

HTH !

Regards

Rahul Sharma

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

Follow me on Twitter |  Follow me on FaceBook