How to get a table storage information

Question

SQL Server Edition: SQL Server 2005 w/ SP3 and 2008

Is there a built-in SQL Server stored procedures that will retrieve following information? Or a DMV (Dynamic Management View) would be great as well.

I am interested mainly on how to find out FILEGROUP data of a table specifically. But it'd be better if there was a sproc that will return all of following result.

By the way, is there any documents that shows one-to-one matching of how to retrieve data that SQL Server UI displays?

Answer 1

The system stored procedure sp_help could be a good starting point.

For example:

exec sp_help 'schema.TableName' 

Answer 2

This will show you all kinds of goodness:

-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0, 2000, and 2005

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

Answer 3

Found a solution.

It seems like it takes longer to type this out than using UI to find out table FILEGROUP information.

Found through List tables in filegroups:

declare @objectid bigint
set @objectid = object_id('table_name')
exec sp_objectfilegroup @objectid

I became too lazy to type those three lines so ended up creating another stored procedure that takes table name instead.

create procedure spTableFileGroup
    @TableName sysname
as
begin
    if exists(  select 1 
                from    INFORMATION_SCHEMA.TABLES T 
                where T.TABLE_NAME = @TableName) begin
        declare @objectid bigint
        set @objectid = object_id(@TableName)
        exec sp_objectfilegroup @objectid
    end
    else begin
        print 'There is no table named "' + @TableName + '"'
    end
end
GO

Usage

exec spTableFileGroup 'table_name'
GO