sql script

DROP TABLE IF EXISTS #t1;

CREATE TABLE #t1 (
    [DBNAME] [sysname] NOT NULL,
    [OBJ_NAME] [sysname] NOT NULL,
    [OBJ_TYPE] [NVARCHAR](60) NULL,
    [RowCounts] [BIGINT] NULL,
    [TotalPages] [BIGINT] NULL,
    [UsedPages] [BIGINT] NULL,
    [DataPages] [BIGINT] NULL,
    [TotalSpaceMB] [BIGINT] NULL,
    [UsedSpaceMB] [BIGINT] NULL,
    [DataSpaceMB] [BIGINT] NULL
) ON [PRIMARY]
GO


DECLARE @command1 varchar(max)
SELECT @command1 =' use ? INSERT INTO #t1 ([DBNAME],[OBJ_NAME],[OBJ_TYPE],[RowCounts],[TotalPages],[UsedPages],[DataPages],[TotalSpaceMB],[UsedSpaceMB],[DataSpaceMB])
SELECT DISTINCT ''?'' AS db_name,t.NAME AS OBJ_NAME, t.type_desc AS OBJ_TYPE, SUM( p.rows ) AS RowCounts, SUM( a.total_pages ) AS TotalPages, SUM( a.used_pages ) AS UsedPages, SUM( a.data_pages ) AS DataPages, (
SUM( a.total_pages ) *8
) /1024 AS TotalSpaceMB, (
SUM( a.used_pages ) *8
) /1024 AS UsedSpaceMB, (
SUM( a.data_pages ) *8
) /1024 AS DataSpaceMB ' +
' FROM sys.objects t ' +
' INNER JOIN sys.schemas s ON t.SCHEMA_ID = s.SCHEMA_ID ' +
' INNER JOIN sys.partitions p ON p.object_id = t.object_id '+
' INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id '+
' WHERE t.type = ''U'' '+
' AND t.NAME NOT LIKE ''dt%'' '+
' AND p.index_id <=1 '+
' GROUP BY s.NAME, t.NAME, t.type_desc, t.object_id '+
' ORDER BY t.name; '
EXEC sp_MSforeachdb @command1

SELECT * FROM #t1