USING sp_spaceused FOR Multiple tables

Using sp_spaceused we can only find statistics for particular table within database in SQL Server. But there is way by which we can USE sp_spaceused for multiple tables too. Actually the fact is that it can be used for all tables and for multiple/all databases. Following script can help you to get space used by all tables in particular database. I will update this script for all/multiple databases soon (hint: use sp_foreachdb along with this scipt to achieve the result) .

-- Declare variables

DECLARE @Table_Size TABLE

(

[Table_Name] NVARCHAR(200)

, [Number_of_Rows] NVARCHAR(100)

, [Reserved_Space] NVARCHAR(100)

, [Data_Space] NVARCHAR(100)

, [Index_Size] NVARCHAR(100)

, [Unused_Space] NVARCHAR(100)

)

DECLARE @Table_Name NVARCHAR(200)

-- Select first table

SELECT TOP 1 @Table_Name = (TABLE_SCHEMA + '.' + TABLE_NAME)

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

ORDER BY (TABLE_SCHEMA + '.' + TABLE_NAME)

-- loop thorugh rest of the tables

WHILE ( @@ROWCOUNT != 0 )

BEGIN

INSERT INTO @Table_Size

EXEC sp_spaceused @objname = @Table_Name

PRINT @Table_Name

SELECT TOP 1 @Table_Name = (TABLE_SCHEMA + '.' + TABLE_NAME )

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND @Table_Name < (TABLE_SCHEMA + '.' + TABLE_NAME)

ORDER BY (TABLE_SCHEMA + '.' + TABLE_NAME)

END

-- Display end results

SELECT * FROM @Table_Size ORDER BY Table_Name