MSSQL Table Size for all tables

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

Comments

  1. says

    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

  2. Zoli says

    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

  3. Mark Stouffer says

    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.

Leave a Reply

Your email address will not be published.