SQL Script to check storage utilized by every database object

Leave a comment (0) Go to comments

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


OUTPUT

SQL Script to check storage utilized by every database object sql server space utilized by every database object size taken by every table sql server number of rows and size taken by every table

EOF - SQL Script to check storage utilized by every database object, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.