RSS

find space utilized by every database object

16 Sep

Following Script will help us to find space utilized by every database object, this includes number of rows and size taken by every table.

DECLARE @projected_size nvarchar(2)
SET @projected_size = NULL

declare        @db_count    bigint,
            @loop_count    bigint,
            @sqlstat    varchar(7000),
            @sqlstat2    varchar(7000),
            @sqlstat3   varchar(7000),
            @sqlstat4   varchar(7000),
            @sqlstat5   varchar(7000),
            @db_name    varchar(150)

CREATE TABLE #tablestats
(
    database_name        varchar(150),
    table_name            varchar(700),
    table_id            bigint,
    data_size            nvarchar(38),
    reserved_size        nvarchar(38),
    unused_size            nvarchar(38),
    index_size            nvarchar(38),
    primary_key_size    nvarchar(38),
    number_rows_table    bigint
)
CREATE TABLE #alltablestats
(
    database_name        varchar(150),
    table_name            varchar(700),
    table_id            bigint,
    data_size            nvarchar(15),
    reserved_size        nvarchar(15),
    unused_size            nvarchar(15),
    index_size            nvarchar(15),
    primary_key_size    nvarchar(15),
    number_rows_table    bigint
)
CREATE TABLE #tabpage
(
    database_name        varchar(150),
    table_name            varchar(700),
    table_id            bigint,
    page_size            dec(30,0),
    used_size            dec(30,0),
    index_size            dec(30,0)
)

CREATE TABLE #pkhold
(
    pk_id                bigint,
    pk_name              varchar(400),
    parent_id            bigint,
    pk_size                nvarchar(15)
)
CREATE TABLE #capacity
(
    tb_id                bigint identity,
    database_name        varchar(150),
    database_id            bigint
)

IF @projected_size  IS null or @projected_size = ‘0’
  SET @projected_size = ‘1’

SET @loop_count = 1

/*****************************************************************************
***get database names on server                                            ***
******************************************************************************/
INSERT INTO #capacity(database_name,database_id)
SELECT DISTINCT name,dbid
FROM master..sysdatabases

SELECT @db_count = count(*)
FROM #capacity

WHILE @loop_count <= @db_count
BEGIN
    SELECT @db_name = database_name
    FROM #capacity
    WHERE tb_id = @loop_count   

/*****************************************************************************
***get table name,id, and data size                                        ***
******************************************************************************/
    SET @sqlstat = ‘use ‘ +  @db_name   

    SET @sqlstat2 = ‘ insert into #tablestats(database_name,table_name,table_id)
             SELECT ”’+ @db_name + ”’,name, ID FROM SYSOBJECTS WHERE XTYPE =”U”’
    EXEC (@sqlstat + @sqlstat2)

    SET @sqlstat2 = ‘ insert into #tabpage(database_name,table_name,table_id,page_size,used_size)
             select distinct database_name,table_name, table_id, ”page size” = (SELECT isnull(SUM(DPAGES),0)
                  FROM SYSINDEXES
                WHERE SYSINDEXES.INDID < 2 AND
                                      SYSINDEXES.ID = #tablestats.table_id),’
    SET @sqlstat3 = ”’used size” = (SELECT ISNULL(SUM(USED),0)
                FROM SYSINDEXES
                WHERE SYSINDEXES.INDID = 255 AND
                                      SYSINDEXES.ID = #tablestats.table_id)
               from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3)

    SET @sqlstat2 = ‘ update #tablestats set data_size = (select LTRIM(STR((page_size + used_size ) ‘
    SET @sqlstat3 = ‘* 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) from  #tabpage ‘
    SET @sqlstat4 = ‘where #tabpage.table_id = #tablestats.table_id and #tabpage.table_name = #tablestats.table_name) from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get reserved size                                                       ***
******************************************************************************/
    SET @sqlstat2 = ‘ update #tablestats set reserved_size = (SELECT LTRIM(STR(cast(SUM(RESERVED) as bigint) ‘
    SET @sqlstat3 = ‘  * 8192 / 1024.*’+@projected_size+’,300,0) + ” ” + ”KB”) FROM SYSINDEXES ‘
    SET @sqlstat4 = ‘ WHERE SYSINDEXES.INDID IN (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get unused size                                                         ***
******************************************************************************/
    SET @sqlstat2 = ‘ update #tablestats set unused_size = (SELECT LTRIM(STR((cast(SUM(RESERVED) as bigint)- SUM(USED)) ‘
    SET @sqlstat3 = ‘ * 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) FROM SYSINDEXES ‘
    SET @sqlstat4 = ‘ WHERE SYSINDEXES.INDID IN (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get index size                                                          ***
******************************************************************************/
    DELETE
    FROM #tabpage

    SET @sqlstat2 = ‘ insert into #tabpage(table_id,page_size,used_size,index_size)select table_id, ‘
    SET @sqlstat3 = ‘ ”page size” = (SELECT SUM(DPAGES) FROM SYSINDEXES WHERE SYSINDEXES.INDID < 2 AND SYSINDEXES.ID = #tablestats.table_id), ‘
    SET @sqlstat4 = ‘ ”used size” = (SELECT SUM(convert(dec(15),USED)) FROM SYSINDEXES WHERE SYSINDEXES.INDID in (255) AND SYSINDEXES.ID = #tablestats.table_id), ‘
    SET @sqlstat5 = ‘ ”index_size” = (SELECT SUM(convert(dec(15),USED))FROM SYSINDEXES WHERE SYSINDEXES.INDID in (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats  ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4+@sqlstat5)

    SET @sqlstat2 = ‘ update #tablestats set INDEX_size = (SELECT LTRIM(STR((index_SIZE – (PAGE_SIZE + ISNULL(USED_SIZE,0))) ‘
    SET @sqlstat3 = ‘ * 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) FROM  #TABPAGE ‘
    SET @sqlstat4 = ‘WHERE #tabPAGE.table_id = #tablestats.table_id) from #tablestats’
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get PK size                                                             ***
******************************************************************************/
    SET @sqlstat2 = ‘ INSERT INTO #PKHOLD(PK_ID,PK_NAME,PARENT_ID) SELECT SO.ID, ‘
    SET @sqlstat3 = ‘ SO.NAME,SO.PARENT_OBJ FROM SYSOBJECTS SO INNER JOIN #tablestats TS ON ‘
    SET @sqlstat4 = ‘ SO.PARENT_OBJ = TS.TABLE_ID WHERE SO.XTYPE = ”PK”’
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)

    UPDATE #tablestats
    SET primary_key_size = index_size
    FROM #PKHOLD  pk INNER JOIN #tablestats tb
              ON pk.PARENT_ID = tb.table_id
/*****************************************************************************
***get number rows                                                         ***
******************************************************************************/
    SET @sqlstat2 = ‘ update #tablestats set number_rows_table = rows*’+@projected_size
    SET @sqlstat3 = ‘ from sysindexes si inner join #tablestats tb on si.id = tb.table_id ‘
    SET @sqlstat4 = ‘ where si.indid < 2 ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)

     INSERT INTO #alltablestats(database_name,table_name,table_id,data_size,
                               reserved_size,unused_size,index_size,
                               primary_key_size,number_rows_table)
    SELECT database_name,table_name,table_id,data_size,
           reserved_size,unused_size,index_size,
           primary_key_size,number_rows_table
    FROM #tablestats
    ORDER BY database_name

     DELETE FROM #tablestats
     DELETE FROM #tabpage
    DELETE FROM #pkhold
    SET @loop_count = @loop_count + 1
 END
/*****************************************************************************
***return results to the screen                                            ***
******************************************************************************/
SELECT database_name,table_name,data_size,
       reserved_size,unused_size,index_size,
       primary_key_size,number_rows_table
FROM  #alltablestats
ORDER BY DATABASE_NAME,TABLE_NAME

 
Leave a comment

Posted by on September 16, 2011 in SQl server Administration

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s