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 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.