Help with adding a new column with output of sp_spaceused using sp_msforeachtable

Question

Dear Experts,

I have this T-SQL with me, it is working fine. However, I need to add a column which returns schema name for the table as well. I see sp_spaceused does not have schema name column. Is there any way I can add to this?
I tried adding using the default schema option schema_name() but it returns only dbo all the time even when the schema is different. Could you please advise?

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

CREATE TABLE #Result
( [RunDate] [datetime] DEFAULT (getdate()),
[DatabaseName] sysname NOT NULL DEFAULT DB_NAME(),
--[SchemaName] sysname default SCHEMA_NAME(),
[TableName] nvarchar(128) NOT NULL,
[NumRows] int NOT NULL,
[Reserved] nchar(16) NULL,
[DataUsed] nchar(16) NULL,
[IndexUsed] nchar(16) NULL,
[Unused] nchar(16) NULL,
);

EXECUTE sys.sp_MSforeachdb
N'USE [?];
IF ''?'' NOT IN (''tempdb'', ''master'', ''model'', ''msdb'')
EXECUTE sys.sp_MSforeachtable
N''INSERT #Result ([TableName], [NumRows], [Reserved], [DataUsed], [IndexUsed], [Unused]) EXECUTE sys.sp_spaceused [!];'',
N''!'';
';

SELECT * FROM #Result AS R;

Answer 1

Hi,@mo boy

Try the following code:

alter PROC dbo.sp_DBA_spaceused_AllTables2  
AS  
SET NOCOUNT ON  
SELECT  
DB_NAME() AS DatabaseNase ,  
GETDATE() AS DateCreated ,  
TableName,  
Rows ,  
Reserved ,  
Data ,  
Used - Data AS IndexSize ,  
Reserved - Used AS Unused  
FROM (  
SELECT   
USR.name + '.' + OBJ.name AS TableName ,  
MIN(row_count) AS Rows ,  
SUM(8 * reserved_page_count) + MAX(COALESCE(LOBDATA.LobReserved,0)) AS reserved,  
SUM (8*  
CASE  
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
ELSE lob_used_page_count + row_overflow_used_page_count  
END  
) AS Data,  
SUM (8*used_page_count)+MAX(COALESCE(LOBDATA.LobUsed,0)) AS Used  
FROM dbo.sysobjects AS OBJ  
INNER JOIN sys.schemas AS USR  
ON OBJ.uid = USR.schema_id  
INNER JOIN sys.dm_db_partition_stats AS PS  
ON PS.object_id = OBJ.id  
LEFT JOIN(  
SELECT   
parent_id,  
SUM(8*reserved_page_count) AS LOBReserved,  
SUM(8*used_page_count) AS LOBUsed  
FROM sys.dm_db_partition_stats p   
INNER JOIN sys.internal_tables it  
ON p.object_id = it.object_id  
WHERE it.internal_type IN (202,204)  
GROUP BY IT.parent_id  
) AS LOBDATA  
ON LOBDATA.parent_id = OBJ.Id  
  
WHERE OBJ.type='U'  
GROUP BY USR.name + '.' + OBJ.name  
) AS DT  
return;  
exec master.dbo.sp_msforeachdb @command1="exec [?].dbo.sp_DBA_spaceused_AllTables2"  
  
CREATE TABLE ##SpaceUsedResults (  
DatabaseName SYSNAME NOT NULL,  
DateCreated DATETIME NOT NULL,  
TableName NVARCHAR(256) NOT NULL,  
Rows BIGINT NOT NULL,  
Reserved BIGINT NOT NULL,  
Data BIGINT NOT NULL,  
IndexSize BIGINT NOT NULL,  
Unused BIGINT NOT NULL,  
CONSTRAINT PK_SpaceUsed PRIMARY KEY CLUSTERED (DatabaseName,TableName,DateCreated)  
  
)  
  
exec master.dbo.sp_msforeachdb @command1="INSERT INTO ##SpaceUsedResults exec [?].dbo.sp_DBA_spaceused_AllTables2"  
SELECT * FROM ##SpaceUsedResults  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

A query based from this blog:
sql-server-how-to-run-sp_spaceused-for-all-tables-in-a-database

declare @PageSize float  
select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'  
   
Select object_Name(i.object_id) as [name]  
,s.name schemaname  
,p.rows  
,Convert(varchar(50),@PageSize * SUM(total_pages)) + ' KB' as [reserved]  
,Convert(varchar(50),@PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) + ' KB' as [data]  
,Convert(varchar(50),@PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) +  ' KB' as [index_size]  
,Convert(varchar(50),@PageSize * SUM(total_pages-used_pages)) +  ' KB' as [unused]  
FROM sys.indexes as i  
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id  
JOIN sys.allocation_units as a ON a.container_id = p.partition_id  
JOIN sys.tables t ON i.object_id=t.object_id  
Join sys.schemas s on s.schema_id=t.schema_id  
Where i.type<=1 and a.type=1  
and  
t.type='U' and is_ms_shipped=0  
Group By i.object_id,p.rows,s.name  
   

Answer 3

Is there any way I can add to this?

No, you can't modify the resultset of a (system) stored procedure in any way.