xUDYhF0r

1DROP TABLE IF EXISTS #t1;
2
3CREATE TABLE #t1 (
4	[DB_NAME] [sysname] NOT NULL,
5	[OBJ_NAME] [sysname] NOT NULL,
6	[OBJ_TYPE] [NVARCHAR](60) NULL,
7	[RowCounts] [BIGINT] NULL,
8	[TotalPages] [BIGINT] NULL,
9	[UsedPages] [BIGINT] NULL,
10	[DataPages] [BIGINT] NULL,
11	[TotalSpaceMB] [BIGINT] NULL,
12	[UsedSpaceMB] [BIGINT] NULL,
13	[DataSpaceMB] [BIGINT] NULL
14) ON [PRIMARY]
15GO
16
17
18DECLARE @command1 varchar(max) 
19SELECT @command1 =' INSERT INTO #t1 ([DB_NAME],[OBJ_NAME],[OBJ_TYPE],[RowCounts],[TotalPages],[UsedPages],[DataPages],[TotalSpaceMB],[UsedSpaceMB],[DataSpaceMB]) 
20SELECT 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, (
21SUM( a.total_pages ) *8
22) /1024 AS TotalSpaceMB, (
23SUM( a.used_pages ) *8
24) /1024 AS UsedSpaceMB, (
25SUM( a.data_pages ) *8
26) /1024 AS DataSpaceMB ' +
27' FROM sys.objects t ' +
28' INNER JOIN sys.schemas s ON t.SCHEMA_ID = s.SCHEMA_ID ' +
29' INNER JOIN sys.partitions p ON p.object_id = t.object_id '+
30' INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id '+
31' WHERE t.type = ''U'' '+
32' AND t.NAME NOT LIKE ''dt%'' '+
33' AND p.index_id <=1 '+
34' GROUP BY s.NAME, t.NAME, t.type_desc, t.object_id '+
35' ORDER BY t.name; '
36EXEC sp_MSforeachdb @command1
37
38SELECT * FROM #t1