For a client's site that uses MS SQL Server, we needed a quick and easy way to see the space occupied by all tables in the database. We found this great script here that does exactly that:
declare @RowCount int, @tablename varchar(100)
declare @Tables table (
PK int IDENTITY(1,1),
tablename varchar(100),
processed bit
)
INSERT into @Tables (tablename)
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc
declare @Space table (
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)
)
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = 1
WHILE (@RowCount <> 0)
BEGIN
insert into @Space exec sp_spaceused @tablename
update @Tables set processed = 1 where tablename = @tablename
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = @@RowCount
END
update @Space set data = replace(data, 'KB', '')
update @Space set data = convert(decimal(8,2), data)/1000
update @Space set data = data + 'MB'
update @Space set reserved = replace(reserved, 'KB', '')
update @Space set reserved = convert(decimal(8,2), reserved)/1000
update @Space set reserved = reserved + 'MB'
select * from @Space order by convert(decimal(8,2), replace(data, 'MB', '')) desc