How to run sp_spaceused for all tables in all databases

SQL Server provides the sp_spaceused stored procedure, which can report on the space used for tables in a database . If we want to find all database sizes , we have to execute   sp_spaceused for all databases.My script will provide all database sizes with using sp_spaceused.

CREATE PROC uspGetALLDBTablesSpaceused

--SAMPLE:uspGetALLDBTablesSpaceused

--Version: SQL Server 2000 and 2008R2

--TO Return a table with the space used in all tables of the ALL databases

--CREATE BY:RYO 20110701

AS

IF OBJECT_ID('tempdb..#ALLTablesSpace') IS NOT NULL

BEGIN

DROP TABLE #ALLTablesSpace

END

IF OBJECT_ID('tempdb..#ALLDBTablesSpace') IS NOT NULL

BEGIN

DROP TABLE #ALLDBTablesSpace

END

IF OBJECT_ID('tempdb..#ALLDBTables') IS NOT NULL

BEGIN

DROP TABLE #ALLDBTables

END

CREATE TABLE #ALLDBTablesSpace (

                         DBNAME  sysname

                       , TabName sysname

                       , [Rows] varchar (11)

                       , Reserved varchar (18)

                       , Data varchar (18)

                       , Index_Size varchar ( 18 )

                       , Unused varchar ( 18 )

                       )

                      

CREATE TABLE #ALLTablesSpace (

                        TabName sysname

                       , [Rows] varchar (11)

                       , Reserved varchar (18)

                       , Data varchar (18)

                       , Index_Size varchar ( 18 )

                       , Unused varchar ( 18 )

                       )                      

CREATE TABLE #ALLDBTables  (DBNAME  sysname,[schema] sysname, TabName sysname )

DECLARE @Tab sysname -- table name

      , @Sch sysname -- owner,schema

     

DECLARE @DATABASESQL        NVARCHAR (4000)

DECLARE @ALLTablesSpaceSQL  NVARCHAR (4000)

DECLARE @ALLDBTablesSpace   NVARCHAR (4000)                  

DECLARE @DATABASENAME VARCHAR(50)                

DECLARE DataBaseCursor CURSOR FOR

SELECT NAME FROM MASTER.DBO.SYSDATABASES

WHERE name NOT IN ('master','tempdb','model','msdb')

OPEN DataBaseCursor;

FETCH DataBaseCursor into @DATABASENAME;           

WHILE @@FETCH_STATUS = 0 BEGIN

        SELECT @DATABASESQL = 'INSERT INTO #ALLDBTables SELECT '+''''+@DATABASENAME+''''+' ,TABLE_SCHEMA, TABLE_NAME FROM ['+@DATABASENAME+'].INFORMATION_SCHEMA.TABLES

        WHERE TABLE_TYPE = ''BASE TABLE''' ;

    --PRINT (@DATABASESQL); 

    EXEC  (@DATABASESQL);

   

   

   

   

                DECLARE TableCursor CURSOR FOR   SELECT [SCHEMA], TabNAME FROM #ALLDBTables WHERE DBNAME=@DATABASENAME

                        OPEN TableCursor;

                        FETCH TableCursor into @Sch, @Tab;

                        WHILE @@FETCH_STATUS = 0

                        BEGIN

                            SELECT @ALLTablesSpaceSQL = 'exec [' + @DATABASENAME

                               + ']..sp_executesql N''insert #ALLTablesSpace exec sp_spaceused '

                               + '''''[' + @Sch + '].[' + @Tab + ']' + '''''''

                              

                               INSERT #ALLDBTablesSpace SELECT '+''''+ @DATABASENAME+''''+',* FROM #ALLTablesSpace

                              

                               TRUNCATE TABLE  #ALLTablesSpace

                               ';

                            --PRINT (@ALLTablesSpaceSQL);

                            EXEC  (@ALLTablesSpaceSQL);

                            --SELECT @ALLDBTablesSpace='INSERT #ALLDBTablesSpace SELECT '+''''+ @DATABASENAME+''''+',* FROM #ALLTablesSpace'

                            --PRINT (@ALLDBTablesSpace);

                            FETCH TableCursor into @Sch, @Tab;

                        END;

                CLOSE TableCursor;

                DEALLOCATE TableCursor;

   

   

    FETCH DataBaseCursor INTO @DATABASENAME;

END;

CLOSE DataBaseCursor;

DEALLOCATE DataBaseCursor;         

SELECT * FROM #ALLDBTablesSpace

IF OBJECT_ID('tempdb..#ALLTablesSpace') IS NOT NULL

BEGIN

DROP TABLE #ALLTablesSpace

END

IF OBJECT_ID('tempdb..#ALLDBTablesSpace') IS NOT NULL

BEGIN

DROP TABLE #ALLDBTablesSpace

END

IF OBJECT_ID('tempdb..#ALLDBTables') IS NOT NULL

BEGIN

DROP TABLE #ALLDBTables

END

SampleCode By Picture

To display the result: