Script to analyze table space usage in SQL Server

to analyze table space usage of SQL Server

Added code to show database file sizes. Not really closely related to tables sizes, but a lot of the people who need this want to know why their database it so large, so it may help to know which files, especially the logs, are so large, and if the files have empty space in them.
— 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