Table Information
CREATE TABLE #indexsize( dbname SYSNAME, tablename VARCHAR(128), indexname VARCHAR(128), indexid INT, indexsize INT ); INSERT INTO #indexsize EXEC sp_MSforeachdb 'SELECT "?",
OBJECT_NAME(i.OBJECT_ID,db_id("?")) AS TableName, i.name AS IndexName, i.index_id AS IndexID,
8 * SUM(a.used_pages)/1024 AS Indexsize
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 GROUP BY i.OBJECT_ID,i.index_id,i.name ORDER BY 4 desc'; SELECT i1.DBName, i1.TableName, ( SELECT SUBSTRING(( SELECT ' ,' + indexname + ':' + CONVERT( VARCHAR(15), indexsize) FROM #indexsize i2 WHERE i2.dbname = i1.dbname AND i2.tablename = i1.tablename FOR XML PATH( '' )), 3, 8000)) IndexName, CASE WHEN( SELECT SUBSTRING(( SELECT ' ,' + indexname + ':' + CONVERT( VARCHAR(15), indexsize) FROM #indexsize i2 WHERE i2.dbname = i1.dbname AND i2.tablename = i1.tablename FOR XML PATH( '' )), 3, 8000)) = ''
THEN '0'
ELSE COUNT(*) OVER(PARTITION BY DBName,
TableName)
END IndexCount,
SUM(indexsize) OVER(PARTITION BY DBName,
TableName) TotalIndexSize INTO #temps
FROM #indexsize i1
ORDER BY i1.dbname, i1.tablename, i1.indexname;
CREATE TABLE #nimit( partition_id BIGINT, object_id INT, index_id BIGINT, partition_number INT, hobt_id BIGINT, rows BIGINT, filestream_filegroup_id SMALLINT, data_compression TINYINT, data_compression_desc NVARCHAR(60), [DatabaseName] [VARCHAR](128), SchemaName [SYSNAME] NOT NULL, [TableName] [NVARCHAR](128) NOT NULL, TableType NVARCHAR(128), CTEnabled VARCHAR(20), IsSchemaPublished VARCHAR(60), IsTablePublished VARCHAR(60), IsReplicated VARCHAR(60), IsTrackedbyCDC VARCHAR(60), TotalColumns VARCHAR(60), TableCreateDate [DATETIME] NOT NULL, TableModifyDate [DATETIME] NOT NULL, RowsCount [BIGINT] NULL, TotalSize [BIGINT] NULL, DataSize [BIGINT] NULL, IndexSize [BIGINT] NULL, UnusedSize [BIGINT] NULL ); INSERT INTO #nimit ( partition_id, object_id, index_id, partition_number, hobt_id, rows, filestream_filegroup_id, data_compression, data_compression_desc, [DatabaseName], SchemaName, [TableName], TableType, CTEnabled, IsSchemaPublished, IsTablePublished, IsReplicated, IsTrackedbyCDC, TotalColumns, TableCreateDate, TableModifyDate, RowsCount, TotalSize, DataSize, IndexSize, UnusedSize
)
EXEC sp_msforeachdb
'SELECT SP.*, db_name(db_id("?")) DatabaseName, a3.name AS [schemaname], a2.name AS [tablename], a2.type, CASE WHEN ctt.object_id IS NULL THEN ''No''
when ctt.object_id is not null then ''Yes'' end CTEnable, ST.is_schema_published, ST.is_published, ST.is_replicated, ST.is_tracked_by_cdc, ST.max_column_id_used, a2.create_date, a2.modify_date, a1.rows as row_count, (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, a1.data * 8 AS data, (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size, (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END
) AS data,
SUM (ps.used_page_count) AS used
FROM ?.sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM ?.sys.dm_db_partition_stats ps
INNER JOIN ?.sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN ?.sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN ?.sys.schemas a3 ON (a2.schema_id = a3.schema_id) left JOIN ?.sys.tables ST on ST.object_id = a2.object_id left JOIN ?.sys.partitions SP on SP.object_id = ST.object_id left JOIN ?.sys.change_tracking_tables CTT on a2.object_id = CTT.object_id --WHERE db_name(db_id("?")) not in (''master'',''model'',''tempdb'',''msdb'')
';
SELECT DISTINCT [DatabaseName],
SchemaName,
n.TableName,
TableType,
CTEnabled,
ISNULL(IsSchemaPublished, 'System Table') IsSchemaPublished, ISNULL(IsTablePublished, 'System Table') IsTablePublished, ISNULL(data_compression_desc, 'System Table') DataCompressionDescription, ISNULL(IsReplicated, 'System Table') IsReplicated, ISNULL(IsTrackedbyCDC, 'System Table') IsTrackedbyCDC, ISNULL(TotalColumns, 'System Table') TotalColumns, TableCreateDate, TableModifyDate, RowsCount, TotalSize, DataSize, n.IndexSize, UnusedSize, i.IndexName [IndexName:Size], i.IndexCount FROM #nimit n LEFT JOIN #temps i ON n.DatabaseName = i.dbname AND n.TableName = i.tablename --where RowsCount > 10000 ORDER BY TotalSize DESC; GO DROP TABLE #nimit; GO DROP TABLE #temps; GO DROP TABLE #indexsize;
posted by Unknown @ 7:14 AM
0 Comments
