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 thoughts on “How much space do my SQL Server tables use?

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

  2. Pingback: Table and index size Per Table. | kristof dba

Comments are closed.