Jun
12
2007

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

Share and Enjoy:
  • del.icio.us
  • digg
  • Furl
  • NewsVine
  • RawSugar
  • Reddit
  • Spurl
  • YahooMyWeb
Written by Tom in: SQL Server |

7 Comments »

  • Alfred Dahl

    Thanks,

    greatly appreciated – works like a charm :)

    Comment | June 19, 2008
  • nice one, thanks!

    Comment | July 4, 2008
  • 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

    Comment | July 4, 2008
  • Tom

    That’s great. Thanks

    Comment | July 4, 2008
  • Zoli

    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

    Comment | October 17, 2008
  • Mark Stouffer

    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.

    Comment | November 13, 2008
  • Dino

    Tnx!

    Comment | March 11, 2009

RSS feed for comments on this post.

Leave a comment


nine × = 63

Powered by WordPress | Aeros Theme | TheBuckmaker.com WordPress Themes