How much space do my SQL Server tables use?

The built-in stored procedure sp_spaceused generates a resultset containing the size of the database, or, when passed a table name, the size of a database table.

Using a cursor to iterate through all user tables, one can easily get a list of all tables and their sizes:

create table #t
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)

declare @id nvarchar(128)
declare c cursor for
select name from sysobjects where xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

  insert into #t
  exec sp_spaceused @id

  fetch c into @id

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

6 Responses to How much space do my SQL Server tables use?

  1. daniel says:

    Great !
    I used it (script) and solve my dudes….

  2. Ahmad says:

    Thanks for your script,
    What is the “unused” space and how can I reduce it?
    Thanks again

  3. Amir Ayub says:

    Thanks ……………… nice script

  4. Umasankar says:

    it is quite helpful. thanks

  5. Houlie says:

    thanks for that, works nicely

%d bloggers like this: