Finding all Columns of an Index

Copy and paste the script in a database to analize, then replace de variable @tblName with the table name to analize and execute
/************ Identify all columns of all indexes on a table***********/

DECLARE @tblName AS sysname
select @tblName =’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 =@tblName
ORDER BY sys.objects.name, sys.indexes.name

Advertisements
This entry was posted in SQL and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s