As far as I could tell there was no easy way to query MSSQL to get a list of all of its tables and their respective sizes. I wrote a sql script that does this for you.
It’s below:
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(int, data)/1000
update @Space set data = data + ' MB'
update @Space set reserved = replace(reserved, ' KB', '')
update @Space set reserved = convert(int, reserved)/1000
update @Space set reserved = reserved + ' MB'
select * from @Space order by convert(int, replace(data, ' MB', '')) desc
Thanks,
greatly appreciated – works like a charm 🙂
nice one, thanks!
BTW: If you are only interested in Tables that contain data/aren’t empty, exchange the last select statement with this one:
select * from @Space where data NOT LIKE ‘0 MB’ order by convert(int, replace(data, ‘ MB’, ”)) desc
That’s great. Thanks
Thank for the code. I’ve made some modifications for better performance and the different schema usage:
declare @tablename varchar(100),@tableschema varchar(100), @tt varchar(100)
DECLARE @partitions bigint;
declare @Space table (
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)
)
select s.name as tableschema, o.name as tablename
into #work_to_do
FROM sys.objects as o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.type_desc = ‘USER_TABLE’
order by o.name
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN partitions;
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @tableschema, @tablename;
IF @@FETCH_STATUS < 0 BREAK;
SET @tt = @tableschema + N’.’ + @tablename
insert into @Space exec sp_spaceused @tt
END;
update @Space set data = replace(data, ‘ KB’,”)
update @Space set data = convert(int, data)/1000
update @Space set reserved = replace(reserved, ‘ KB’,”)
update @Space set reserved = convert(int, reserved)/1000
update @Space set index_size = replace(index_size, ‘ KB’,”)
update @Space set index_size = convert(int, index_size)/1000
update @Space set unused = replace(unused, ‘ KB’,”)
update @Space set unused = convert(int, unused)/1000
CLOSE partitions;
DEALLOCATE partitions;
drop table #work_to_do
select top 500 * from @Space order by convert(int, data) desc
Wow, thanks. I was just about to write this myself. The space used is great but I don’t want to run it for every table.
Tnx!