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
