DECLARE @objtable TABLE
\n(
\ndbname VARCHAR(100) NULL,
\nSizeMB INT
\n)<\/p>\n
insert into @objtable
\nexec sp_msforeachdb
\n‘use [?];
\nSELECT ”?”, MAX(TotalSpaceMB) as MaxObjSizeMB
\nFROM (
\nSELECT
\n t.NAME AS TableName,
\n i.name as indexName,
\n (sum(a.total_pages) * 8) \/ 1024 as TotalSpaceMB
\nFROM
\n sys.tables t
\nINNER JOIN
\n sys.indexes i ON t.OBJECT_ID = i.object_id
\nINNER JOIN
\n sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
\nINNER JOIN
\n sys.allocation_units a ON p.partition_id = a.container_id
\nWHERE
\n t.NAME NOT LIKE ”dt%” AND
\n i.OBJECT_ID > 255 AND
\n i.index_id <= 1 AND\n\t''?'' <> ”TEMPDB”
\nGROUP BY
\n t.NAME, i.object_id, i.index_id, i.name ) as Tab’<\/p>\n
select * from @objtable order by SizeMB desc<\/p>\n","protected":false},"excerpt":{"rendered":"
DECLARE @objtable TABLE ( dbname VARCHAR(100) NULL, SizeMB INT ) insert into @objtable exec sp_msforeachdb ‘use [?]; SELECT ”?”, MAX(TotalSpaceMB) as MaxObjSizeMB FROM ( SELECT t.NAME AS TableName, i.name as indexName, (sum(a.total_pages) * 8) \/ 1024 as TotalSpaceMB FROM sys.tables … Continue reading