3

How to execute sp_spaceused for all tables in all databases?

i.e. how to execute sp_spaceused inside sp_msforeachdb.

e.g:

 sp_msforeachdb 'sp_msforeachtable'sp_spaceused"?"''

4 Answers 4

6

Use dynamic SQL with a cursor (for these cases it is ok)

Here is the code:

DECLARE @DBName varchar(200)
DECLARE @CMD varchar(max)
DECLARE @TV TABLE ( name_table varchar(500),ROWSCOUNT int, reserveder varchar(50), datasize varchar(50), indexsize varchar(50), unused varchar(50))
DECLARE @TV2 TABLE ( DBName varchar(200),name_table varchar(500),ROWSCOUNT int, reserveder varchar(50), datasize varchar(50), indexsize varchar(50), unused varchar(50))
DECLARE @TV3 TABLE ( DBName varchar(200),SchemaTAble varchar(500), TableName varchar(200))
DECLARE C Cursor for select quotename(name) as dbname from sys.databases where name <> 'tempdb'  and state = 0

OPEN C

FETCH NEXT FROM C INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE FROM @TV3
    SET @CMD = 
    @DBName+'..sp_msforeachtable ''exec sp_spaceused [?]'''
    insert into @TV
    EXEC (@CMD)

    SET @CMD = 'select '''+@DBName+''' as DatabaseName, quotename(s.name)+''.''+quotename(t.name) as SchemaTable, quotename(t.name) TableName from '+@DBName+'.sys.tables t inner join '+@DBName+'.sys.schemas s on s.schema_id = t.schema_id '
    insert into @TV3
    EXEC (@CMD)

    insert into @TV2
    select @DBName ,t.name_table,t.ROWSCOUNT,t.reserveder,t.datasize,t.indexsize,t.unused from @TV t
    inner join @TV3 s on quotename(t.name_table) = s.TableName and s.DBName = @DBName

    FETCH NEXT FROM C INTO @DBNAME
END
CLOSE C
DEALLOCate C

select * from @TV2

There is a difference in usage for sp_spaceused in SQL Server 2016 and SQL Server 2012. Note that this code will only work for SQL Server 2012.

0
1

In order to do this successfully, you need to be able to uniquely identify your tables, which is not possible using sp_spaceused unless you also qualify Database, and schema.

Not doing so creates a defect, because you may have duplicate table names in either a different schema, or in a different database, e.g. you use a staging schema, or staging database to handle delta loads, and then an ODS (operational data store) table of the same name, or you have multiple semantic views separated by database or schema.

My final SELECT is only utilizing three of the four sp_spaceused attribute columns, because I derive the "Reserved" column from data + index + unused.

By using a couple cursors, along with an UPDATE statement, you can reliably match the sp_spaceused results with each unique database object:

SET NOCOUNT ON

IF OBJECT_ID(N'tempdb.dbo.#ReadDatabases') IS NOT NULL
DROP TABLE #ReadDatabases;

IF OBJECT_ID(N'tempdb.dbo.#AllTables') IS NOT NULL
DROP TABLE #AllTables;

IF OBJECT_ID(N'tempdb.dbo.#stats') IS NOT NULL
DROP TABLE #stats;

DECLARE @sql NVARCHAR(MAX)
DECLARE @DatabaseName VARCHAR(255)
DECLARE @SchemaName VARCHAR(255)
DECLARE @TableName VARCHAR(255)

CREATE TABLE #ReadDatabases
    (
    ID INT IDENTITY (1,1) NOT NULL,
    DatabaseName VARCHAR(255) NOT NULL,
    IsReadable BIT NOT NULL
    );

CREATE TABLE #AllTables
    (
    ID INT IDENTITY (1,1) NOT NULL,
    DatabaseName VARCHAR(255) NOT NULL,
    SchemaName VARCHAR(255) NOT NULL,
    TableName VARCHAR(255) NOT NULL
    )

CREATE TABLE #stats --  minimally requires sp_spaceused's output columns
    (
    ID INT IDENTITY (1,1) NOT NULL,
    DatabaseName VARCHAR(255) NULL, --  keep null as sp_spaceused result will be inserted row-by-row, and then this column will be updated at end of each cursor iteration
    SchemaName VARCHAR(255) NULL,   --  keep null as sp_spaceused result will be inserted row-by-row, and then this column will be updated at end of each cursor iteration
    TableName VARCHAR(255) NOT NULL,
    RowCounts BIGINT NOT NULL,
    Reserved VARCHAR(20) NOT NULL,
    [Data]  VARCHAR(20) NOT NULL,
    index_size VARCHAR(20) NOT NULL,
    unused VARCHAR(20) NOT NULL
    )

DECLARE db CURSOR LOCAL FAST_FORWARD FOR
    SELECT 
        name
    FROM master.sys.databases
    WHERE name NOT IN
        (
        'master',
        'tempdb',
        'model',
        'msdb'
        )

    OPEN db
        WHILE 1 = 1
            BEGIN
                FETCH NEXT FROM db INTO @DatabaseName;
                IF @@FETCH_STATUS != 0
                    BEGIN
                        BREAK
                    END


SET @sql = 
'BEGIN TRY

    INSERT INTO #ReadDatabases
        (
        DatabaseName,
        IsReadable
        )
        SELECT TOP 1 
            ''' + @DatabaseName + ''',
            1
        FROM ' + @DatabaseName + '.sys.tables

END TRY

BEGIN CATCH

    INSERT INTO #ReadDatabases
        (
        DatabaseName,
        IsReadable
        )
        SELECT TOP 1 
            ''' + @DatabaseName + ''',
            0

END CATCH
'


    EXEC (@sql)     --  PRINT (@sql)    --  

            END;
    CLOSE db;
DEALLOCATE db;

DECLARE crsr_tbls CURSOR LOCAL FAST_FORWARD FOR

    SELECT
        DatabaseName
    FROM #ReadDatabases
    WHERE IsReadable = 1
    ORDER BY 1

    OPEN crsr_tbls
        WHILE 1 = 1
            BEGIN
                FETCH NEXT FROM crsr_tbls INTO @DatabaseName;
                IF @@FETCH_STATUS != 0
                    BEGIN
                        BREAK
                    END

SET @sql = 

'
USE ' + @DatabaseName + ';

INSERT INTO #AllTables
    (
    DatabaseName,
    SchemaName,
    TableName
    )
    SELECT
        quotename(''' + @DatabaseName + ''') AS DatabaseName,
        quotename(s.name) AS SchemaName,
        quotename(t.name) AS TableName
    FROM ' + @DatabaseName + '.sys.tables AS t
    INNER JOIN ' + @DatabaseName + '.sys.schemas AS s
        ON t.schema_id = s.schema_id
    ORDER BY s.name,t.name
'

    EXEC sp_executesql @sql;
            
            END;
        CLOSE crsr_tbls;
    DEALLOCATE crsr_tbls;

DECLARE crsr_spaceused CURSOR LOCAL FAST_FORWARD FOR

    SELECT 
        DatabaseName,
        SchemaName,
        TableName 
    FROM #AllTables

    OPEN crsr_spaceused 
        WHILE 1 = 1
            BEGIN
                FETCH NEXT FROM crsr_spaceused INTO @DatabaseName,@SchemaName,@TableName;
                IF @@FETCH_STATUS != 0
                    BEGIN
                        BREAK
                    END

SET @sql =  --  execute recursively against every object
'USE ' + @DatabaseName + ';
EXEC sp_spaceused ''' + REPLACE(@SchemaName,'''','''''') + '.' + REPLACE(@TableName,'''','''''') + '''' + CHAR(10)  --  replace edge case scenarios where a table name includes a single quote

    INSERT INTO #stats
        (
        TableName,
        RowCounts,
        Reserved,
        [Data],
        index_size,
        unused
        )
        EXEC sp_executesql @sql;    --  PRINT (@sql)    --  

    UPDATE #stats   --  update database and schema name of the record that was just inserted
    SET
        DatabaseName = @DatabaseName,
        SchemaName = @SchemaName
    WHERE DatabaseName IS NULL
            
            END;
        CLOSE crsr_spaceused;
    DEALLOCATE crsr_spaceused;
GO

SELECT
    @@SERVERNAME AS ServerName,
    REPLACE(REPLACE(DatabaseName,'[',''),']','') AS DatabaseName,
    REPLACE(REPLACE(SchemaName,'[',''),']','') AS SchemaName,
    REPLACE(REPLACE(TableName,'[',''),']','') AS TableName,
    RowCounts,
    REPLACE([Data],' KB','') AS DataSpaceKB,
    REPLACE(index_size,' KB','') AS IndexSpaceKB,
    REPLACE(unused,' KB','') AS UnusedSpaceKB
FROM #stats
1

To answer the question literally, you need to specify a different replacement character for the nested call to sp_MSforeachtable:

EXECUTE sys.sp_MSforeachdb
    N'USE [?];
    IF ''?'' NOT IN (''tempdb'', ''master'', ''model'', ''msdb'')
        EXECUTE sys.sp_MSforeachtable
            N''EXECUTE sys.sp_spaceused [!];'',
            N''!'';
    ';

But that produces a separate result set per table, and does not tell you which database each table was found in. We can address both problems using a temporary table:

IF OBJECT_ID(N'tempdb..#Result', 'U') IS NOT NULL
    DROP TABLE #Result;

CREATE TABLE #Result
(
    db sysname NOT NULL DEFAULT DB_NAME(),
    [name] sysname NOT NULL,
    [rows] char(20) NOT NULL,
    reserved char(18) NULL,
    [data] char(18) NULL,
    index_size  char(18) NULL,
    unused char(18) NULL,
);

EXECUTE sys.sp_MSforeachdb
    N'USE [?];
    IF ''?'' NOT IN (''tempdb'', ''master'', ''model'', ''msdb'')
    EXECUTE sys.sp_MSforeachtable
        N''INSERT #Result ([name], [rows], reserved, [data], index_size, unused) 
           EXECUTE sys.sp_spaceused [!];'',
        N''!'';
    ';

SELECT * FROM #Result AS R;
0

This code will insert the data from sp_spaceused per table into a temp table which you then can manipulate the way you feel fit.

    DECLARE @Database TABLE (DbName SYSNAME);
    IF OBJECT_ID('tempdb.dbo.#TableIndexes', 'U') IS NOT NULL 
    BEGIN
      PRINT 'dropping table'
      DROP TABLE tempdb.dbo.#TableIndexes;
    END
     --Setting up temp table    
    CREATE TABLE #TableIndexes (
      TableName nvarchar(128)
      ,NumberOfRows varchar(50)
      ,ReservedSpace varchar(50)
      ,TableDataSpace varchar(50)
      ,IndexSize varchar(50)
      ,unused varchar(50))

    DECLARE @DbName AS SYSNAME;
    DECLARE @Sql AS VARCHAR(MAX);

    SET @DbName = '';

    --Cycle through and get the DBs from your server
    INSERT INTO @Database (DbName)
      SELECT NAME
      FROM sys.databases
      ORDER BY NAME ASC;
    WHILE @DbName IS NOT NULL
     BEGIN
      SET @DbName = (
        SELECT MIN(DbName)
        FROM @Database
        WHERE DbName > @DbName
        );
    --Create SQL statement to insert into temp table for each db
    SET @Sql =
      'USE ' + QUOTENAME(@DbName) + '; ' + '
       Exec sp_msforeachtable
        ''insert into #TableIndexes exec sp_spaceused [?]''
      '
    --Executes stored SQL statement, inserting rows
    Exec (@Sql);
     END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.