SQL Server: getting list of all tables.

The quick way to get the information would be, in Management Studio, right click the database, select reports -> standard reports -> Disk Usage by Table.  But that doesn't give you a query.

Running "sp_msforeachtable 'EXEC sp_spaceused [?]' " within the database will output the information you want, but the output might still not be what you are looking for (?).

That report produces exact the same information like my code.

> That report produces exact the same information like my code.
Yes sir.  At the heart, its the same.  :-/  I gathered my information, and refreshed right before I started typing my answer, and when I finished discovered you had another answer which predated mine by a couple minutes.

Playing with other queries, I found/modified this one which doesn't use sp_spaceused, but relies on DMVs instead:

SELECT object_name(a.[object_id]) as TableName,
sum(a.record_count) as row_count,
sum(a.page_count)*1.0/128 as size_in_Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED') AS a
   JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
group by a.[object_id],a.index_id, b.name

Select allOpen in new window

The problem with sys.dm_db_index_physical_stats is that it takes some time to run, especially if you use DETAILED as parameter. It is usually used to get the index fragmentation.

And by the way, I had no idea that the report produces exact the same data as my query until I checked it after your post. :o) Probably is using the same stored procedure.

View sys.dm_db_partition_stats will give you that info easily and quickly.

The row count may only be approximate though; you can avoid that by issuing a DBCC UPDATEUSAGE first, but that can take quite a bit of time to complete, depending on table sizes.

-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0 to 2008 any version

set nocount on


print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'

select
        [FileSizeMB]   =
               convert(numeric(10,2),sum(round(a.size/128.,2))),
        [UsedSpaceMB]  =
               convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
        [UnusedSpaceMB]        =
               convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
        [Type] =
               case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
        [DBFileName]   = isnull(a.name,'*** Total for all files ***')
from
        sysfiles a
group by
        groupid,
        a.name
        with rollup
having
        a.groupid is null or
        a.name is not null
order by
        case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
        a.groupid,
        case when a.name is null then 99 else 0 end,
        a.name




create table #TABLE_SPACE_WORK
(
        TABLE_NAME     sysname        not null ,
        TABLE_ROWS     numeric(18,0)  not null ,
        RESERVED       varchar(50)    not null ,
        DATA           varchar(50)    not null ,
        INDEX_SIZE     varchar(50)    not null ,
        UNUSED         varchar(50)    not null ,
)

create table #TABLE_SPACE_USED
(
        Seq            int            not null      
        identity(1,1)  primary key clustered,
        TABLE_NAME     sysname        not null ,
        TABLE_ROWS     numeric(18,0)  not null ,
        RESERVED       varchar(50)    not null ,
        DATA           varchar(50)    not null ,
        INDEX_SIZE     varchar(50)    not null ,
        UNUSED         varchar(50)    not null ,
)

create table #TABLE_SPACE
(
        Seq            int            not null
        identity(1,1)  primary key clustered,
        TABLE_NAME     SYSNAME        not null ,
        TABLE_ROWS     int             not null ,
        RESERVED       int             not null ,
        DATA           int             not null ,
        INDEX_SIZE     int             not null ,
        UNUSED         int             not null ,
        USED_MB                        numeric(18,4)  not null,
        USED_GB                        numeric(18,4)  not null,
        AVERAGE_BYTES_PER_ROW          numeric(18,5)  null,
        AVERAGE_DATA_BYTES_PER_ROW     numeric(18,5)  null,
        AVERAGE_INDEX_BYTES_PER_ROW    numeric(18,5)  null,
        AVERAGE_UNUSED_BYTES_PER_ROW   numeric(18,5)  null,
)

declare @fetch_status int

declare @proc  varchar(200)
select  @proc   = rtrim(db_name())+'.dbo.sp_spaceused'

declare Cur_Cursor cursor local
for
select
        TABLE_NAME     =
        rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
        INFORMATION_SCHEMA.TABLES
where
        TABLE_TYPE     = 'BASE TABLE'
order by
        1

open Cur_Cursor

declare @TABLE_NAME    varchar(200)

select @fetch_status = 0

while @fetch_status = 0
        begin

        fetch next from Cur_Cursor
        into
               @TABLE_NAME

        select @fetch_status = @@fetch_status

        if @fetch_status <> 0
               begin
               continue
               end

        truncate table #TABLE_SPACE_WORK

        insert into #TABLE_SPACE_WORK
               (
               TABLE_NAME,
               TABLE_ROWS,
               RESERVED,
               DATA,
               INDEX_SIZE,
               UNUSED
               )
        exec @proc @objname =
               @TABLE_NAME ,@updateusage = 'true'


        -- Needed to work with SQL 7
        update #TABLE_SPACE_WORK
        set
               TABLE_NAME = @TABLE_NAME

        insert into #TABLE_SPACE_USED
               (
               TABLE_NAME,
               TABLE_ROWS,
               RESERVED,
               DATA,
               INDEX_SIZE,
               UNUSED
               )
        select
               TABLE_NAME,
               TABLE_ROWS,
               RESERVED,
               DATA,
               INDEX_SIZE,
               UNUSED
        from
               #TABLE_SPACE_WORK

        end     --While end

close Cur_Cursor

deallocate Cur_Cursor

insert into #TABLE_SPACE
        (
        TABLE_NAME,
        TABLE_ROWS,
        RESERVED,
        DATA,
        INDEX_SIZE,
        UNUSED,
        USED_MB,
        USED_GB,
        AVERAGE_BYTES_PER_ROW,
        AVERAGE_DATA_BYTES_PER_ROW,
        AVERAGE_INDEX_BYTES_PER_ROW,
        AVERAGE_UNUSED_BYTES_PER_ROW

        )
select
        TABLE_NAME,
        TABLE_ROWS,
        RESERVED,
        DATA,
        INDEX_SIZE,
        UNUSED,
        USED_MB                =
               round(convert(numeric(25,10),RESERVED)/
               convert(numeric(25,10),1024),4),
        USED_GB                =
               round(convert(numeric(25,10),RESERVED)/
               convert(numeric(25,10),1024*1024),4),
        AVERAGE_BYTES_PER_ROW  =
               case
               when TABLE_ROWS <> 0
               then round(
               (1024.000000*convert(numeric(25,10),RESERVED))/
               convert(numeric(25,10),TABLE_ROWS),5)
               else null
               end,
        AVERAGE_DATA_BYTES_PER_ROW     =
               case
               when TABLE_ROWS <> 0
               then round(
               (1024.000000*convert(numeric(25,10),DATA))/
               convert(numeric(25,10),TABLE_ROWS),5)
               else null
               end,
        AVERAGE_INDEX_BYTES_PER_ROW    =
               case
               when TABLE_ROWS <> 0
               then round(
               (1024.000000*convert(numeric(25,10),INDEX_SIZE))/
               convert(numeric(25,10),TABLE_ROWS),5)
               else null
               end,
        AVERAGE_UNUSED_BYTES_PER_ROW   =
               case
               when TABLE_ROWS <> 0
               then round(
               (1024.000000*convert(numeric(25,10),UNUSED))/
               convert(numeric(25,10),TABLE_ROWS),5)
               else null
               end
from
        (
        select
               TABLE_NAME,
               TABLE_ROWS,
               RESERVED       =
               convert(int,rtrim(replace(RESERVED,'KB',''))),
               DATA           =
               convert(int,rtrim(replace(DATA,'KB',''))),
               INDEX_SIZE     =
               convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
               UNUSED         =
               convert(int,rtrim(replace(UNUSED,'KB','')))
        from
               #TABLE_SPACE_USED aa
        ) a
order by
        TABLE_NAME

print 'Show results in descending order by size in MB'

select * from #TABLE_SPACE order by USED_MB desc
go

drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED
drop table #TABLE_SPACE
Result.jpg