List tables and indexes, with partitioning, compression, storage, etc.

list_tables_and_indexes.sql

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;