One of the person was asking on forums about how to find the space used by each table in SQL. So I am writing this blog and showing the script will give you a list of tables with spaces used by them.
Here is the script :
DECLARE @temp TABLE
([TableName] VARCHAR(200),[NumberOfRows] BIGINT,[Reserved] VARCHAR(20),
[Data] VARCHAR(20),[Index_Size] VARCHAR(20),[Unused] VARCHAR(20))
INSERT INTO @temp
EXEC sp_MSForEachtable 'EXEC sp_spaceused ''?'''
SELECT * FROM @temp ORDER BY TableName
What I have done above is just created a temp table and I run the sp_MSforeachtable stored procedure with the paramater 'EXEC sp_spaceused ''?'''. sp_MSforeachtable will loop through all the tables in the database. Below is the screenshot of the output which I was getting when I run the same query in AdventureWorks DB.
Do let me know your feedback, comments.