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: