Last active
July 26, 2022 21:43
-
-
Save dhmacher/8dce4f703930460a504d32ce3f2a1b40 to your computer and use it in GitHub Desktop.
List tables and indexes, with partitioning, compression, storage, etc.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT o.[type], | |
ISNULL(NULLIF(t.temporal_type_desc, 'NON_TEMPORAL_TABLE'), o.[type_desc]) AS object_type, | |
s.[name]+'.'+o.[name] AS [object_name], | |
(CASE WHEN i.is_unique=1 THEN 'UNIQUE ' ELSE '' END)+i.[type_desc] AS index_type, | |
(CASE WHEN kc.is_system_named=1 THEN '' ELSE i.[name] END) AS index_name, | |
ISNULL('WHERE '+i.filter_definition, '') AS index_filter, | |
ISNULL(' ON '+(CASE WHEN ds.is_default=0 THEN ds.[name] END)+ISNULL('('+c.[name]+')', ''), '') AS data_space, | |
ISNULL(CAST(NULLIF(NULLIF(i.fill_factor, 0), 100) AS varchar(10))+'%', '') AS fill_factor, | |
ISNULL(CAST(p.partition_number AS varchar(10))+'/'+CAST(NULLIF(MAX(p.partition_number) OVER (PARTITION BY p.[object_id], p.index_id), 1) AS varchar(10)), '') AS [partition], | |
ISNULL(NULLIF(p.data_compression_desc, 'NONE'), '') AS [compression], | |
ISNULL(REPLACE(REPLACE(CONVERT(varchar(15), CAST(NULLIF(p.[rows], 0) AS money), 1), '.00', ''), ',', ' '), '') AS [row_count], | |
ISNULL(REPLACE(CONVERT(varchar(15), CAST(1.*NULLIF(ps.reserved_page_count, 0)*8192/POWER(1024, 2) AS money), 1), ',', ' ')+' MB', '') AS reserved_space, | |
ISNULL(REPLACE(CONVERT(varchar(15), CAST(1.*NULLIF(ps.used_page_count, 0)*8192/POWER(1024, 2) AS money), 1), ',', ' ')+' MB', '') AS used_space, | |
ISNULL(STR(100.*NULLIF(ps.used_page_count, 0)/NULLIF((SELECT SUM(used_page_count) FROM sys.dm_db_partition_stats), 0), 6, 2)+'%', '') database_usage | |
FROM sys.schemas AS s | |
INNER JOIN sys.objects AS o ON s.[schema_id]=o.[schema_id] | |
LEFT JOIN sys.tables AS t ON o.[object_id]=t.[object_id] | |
INNER JOIN sys.indexes AS i ON o.[object_id]=i.[object_id] AND i.is_hypothetical=0 | |
LEFT JOIN sys.key_constraints AS kc ON i.[object_id]=kc.[object_id] AND i.index_id=kc.unique_index_id | |
INNER JOIN sys.data_spaces AS ds ON i.data_space_id=ds.data_space_id | |
LEFT JOIN sys.index_columns AS pc ON pc.[object_id]=o.[object_id] AND i.index_id=pc.index_id AND pc.partition_ordinal=1 AND ds.[type]='PS' | |
LEFT JOIN sys.columns AS c ON c.[object_id]=o.[object_id] AND c.column_id=pc.column_id | |
INNER JOIN sys.partitions AS p ON i.[object_id]=p.[object_id] AND i.index_id=p.index_id | |
LEFT JOIN sys.dm_db_partition_stats AS ps ON p.[partition_id]=ps.[partition_id] | |
WHERE o.[type] NOT IN ('IT', 'S') | |
ORDER BY s.[name], o.[name], i.index_id, p.partition_number; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment