List Table Sizes on All Databases

This post is for Julian...One of my dear trouble makers! :)))

Junlian came to DBA team to ask for a report of table size of all tables on all databases. My instant thought was to use the standard report of disk usage by table. However, no no no, he wanted more. The report only provides the table size for each database and doesn’t list all databases’ table sizes. And then I tried to combine sp_MSforeachdb and sp_MSforeachtable mentioned in the previous post to check all the tables on all the databases. Bad news! They don't work together well. OK, more work, and lets write a script from scratch then. How exciting!

Finally I came up with this solution.

IF Object_id('tempdb..#DatabaseTables') IS NOT NULL

  DROP TABLE #databasetables;

CREATE TABLE #databasetables

  (

     id        INT IDENTITY(1, 1),

     [dbname]  SYSNAME,

     tablename NVARCHAR(200)

  )

IF Object_id('tempdb..#TableSizeStaging') IS NOT NULL

  DROP TABLE #TableSizeStaging;

CREATE TABLE #TableSizeStaging

  (

     tablename  NVARCHAR(200),

     rows       CHAR(11),

     reserved   VARCHAR(18),

     data       VARCHAR(18),

     index_size VARCHAR(18),

     unused     VARCHAR(18)

  )

IF Object_id('tempdb..#rpt_DatabaseTableSizes') IS NOT NULL

  DROP TABLE #rpt_databasetablesizes;

CREATE TABLE #rpt_databasetablesizes

  (

     [dbname]    [VARCHAR](100),

     [tablename] NVARCHAR(200),

     rownumber   INT,

     sizeinkb    INT

  )

DECLARE @SQL NVARCHAR(4000)

SET @SQL=

'select ''?'' AS [Database], ''[''+TABLE_SCHEMA+''].[''+TABLE_NAME+'']'' from [?].information_schema.tables WHERE TABLE_TYPE = ''BASE TABLE'' '

INSERT INTO #databasetables

            (dbname,

             tablename)

EXECUTE Sp_msforeachdb

  @Command1=@SQL

DELETE #databasetables

WHERE  dbname = 'tempdb'

--SELECT * FROM #DatabaseTables

DECLARE @DBName SYSNAME

DECLARE @TName NVARCHAR(128)

DECLARE @count INT

SET @count = 1

WHILE @count <= (SELECT Max(id)

                 FROM   #databasetables)

  BEGIN

      SELECT @DBName = dbname,

             @TName = tablename

      FROM   #databasetables

      WHERE  id = @count

      --SET @TName = REPLACE(@TName, '[','')

      --SET @TName = REPLACE(@TName, ']','');

      TRUNCATE TABLE #TableSizeStaging

      INSERT INTO #TableSizeStaging

      EXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ''' + @TName + '''')

      INSERT INTO #rpt_databasetablesizes

                  (dbname,

                   tablename,

                   rownumber,

                   [sizeinkb])

      SELECT @DBName,

             @TName,

             CONVERT(BIGINT, rows)                              AS RowNumber,

             CONVERT(BIGINT, LEFT(reserved, Len(reserved) - 3)) AS SizeinKB

      FROM   #TableSizeStaging

      SET @count = @count + 1

  END

SELECT *

FROM   #rpt_databasetablesizes

ORDER  BY dbname ASC,

          sizeinkb DESC 

PS. I am not a developer, and the script isn’t perfect, but works. J