Execute `sp_spaceused` for all tables in all databases

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"?"''

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.

StackExchange Database Administrators Q#164977, answer score: 6