Technical Article

Finding all Columns of an Index

,

Copy and paste the script in a database to analize, then replace de variable @nombre with the table name to analize and execute

/************ Identify all columns of all indexes on a table***********/
DECLARE @NOMBRE AS sysname
select @NOMBRE ='TABLE_NAME'
SELECT
 sys.objects.object_id, sys.objects.name AS object_name,
 sys.indexes.index_id, sys.indexes.name AS index_name,
 sys.indexes.type, sys.indexes.type_desc,
 partitions.Rows, partitions.SizeMB,
 sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint,
 ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
 ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include
FROM
 sys.objects
 JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
 JOIN (
 SELECT DISTINCT sys.index_columns.object_id, sys.index_columns.index_id
 FROM
 (
 SELECT object_id, column_id
 FROM sys.index_columns
 WHERE key_ordinal=1 AND is_included_column=0
 GROUP BY object_id, column_id
 ) AS index_columns_dupe
 JOIN sys.index_columns ON index_columns_dupe.object_id=sys.index_columns.object_id AND index_columns_dupe.column_id=sys.index_columns.column_id AND sys.index_columns.key_ordinal=1
 ) AS dupe_index_objects ON sys.objects.object_id=dupe_index_objects.object_id
 JOIN sys.indexes ON sys.objects.object_id=sys.indexes.object_id AND dupe_index_objects.index_id=sys.indexes.index_id
 JOIN (
 SELECT
 object_id, index_id, SUM(row_count) AS Rows,
 CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
 FROM sys.dm_db_partition_stats
 GROUP BY object_id, index_id
 ) AS partitions ON sys.indexes.object_id=partitions.object_id AND sys.indexes.index_id=partitions.index_id
 CROSS APPLY (
 SELECT
 (
 SELECT sys.columns.name + ', '
 FROM
 sys.index_columns
 JOIN sys.columns ON
 sys.index_columns.column_id=sys.columns.column_id
 AND sys.index_columns.object_id=sys.columns.object_id
 WHERE
 sys.index_columns.is_included_column=0
 AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
 ORDER BY key_ordinal
 FOR XML PATH('')
 ) AS index_columns_key,
 (
 SELECT sys.columns.name + ', '
 FROM
 sys.index_columns
 JOIN sys.columns ON
 sys.index_columns.column_id=sys.columns.column_id
 AND sys.index_columns.object_id=sys.columns.object_id
 WHERE
 sys.index_columns.is_included_column=1
 AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY index_column_id
 FOR XML PATH('')
 ) AS index_columns_include
 ) AS Index_Columns
WHERE
 sys.objects.name =@NOMBRE
ORDER BY sys.objects.name, sys.indexes.name

Rate

3.25 (4)

Share

Share

Rate

3.25 (4)