2

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

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.

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.