Finding the size of the Tables inside a DB

The Below code will fetches each Table Name,# of Rows in that table, Reserved Size, Index Size,UnUsed Space in the given Database.

-- ===================================================
-- Author:  V.U.M.Sastry Sagi
-- Create date: 01/12/2011
-- Description: Fetches the tables and the size useage of each table
-- ===================================================

CREATE PROCEDURE [DBO].[SPACEUSED] @SOURCEDB VARCHAR(128)
AS 
    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(128)
    CREATE TABLE #TABLES ( NAME VARCHAR(128) )
	
    SELECT  @SQL = 'INSERT #TABLES SELECT TABLE_NAME FROM ' + @SOURCEDB
            + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
    EXEC ( @SQL
        )
	
    CREATE TABLE #SPACEUSED
        (
          NAME VARCHAR(128),
          ROWS VARCHAR(11),
          RESERVED VARCHAR(18),
          DATA VARCHAR(18),
          INDEX_SIZE VARCHAR(18),
          UNUSED VARCHAR(18)
        )
    DECLARE @NAME VARCHAR(128)
    SELECT  @NAME = ''
    WHILE EXISTS ( SELECT   *
                   FROM     #TABLES
                   WHERE    NAME > @NAME )
        BEGIN
            SELECT  @NAME = MIN(NAME)
            FROM    #TABLES
            WHERE   NAME > @NAME
            SELECT  @SQL = 'EXEC ' + @SOURCEDB
                    + '..SP_EXECUTESQL N''INSERT #SPACEUSED EXEC SP_SPACEUSED '
                    + @NAME + ''''
            EXEC ( @SQL
                )
        END
    SELECT  *
    FROM    #SPACEUSED
    DROP TABLE #TABLES
    DROP TABLE #SPACEUSED

2 Comments