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:
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
--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: