Clash Royale CLAN TAG#URR8PPP

List all indexes with included columns(nonkeys)

I had tried sp_helpindex but it shows the columns that have index not the included columns. Please tell me how to list all indexes with included columns(nonkeys)?

3 Answers
3

Try this T-SQL query against the catalog views:

SELECT
IndexName = i.Name,
ColName = c.Name
FROM
sys.indexes i
INNER JOIN
sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN
sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE
ic.is_included_column = 1
ORDER BY
i.Name


It basically checks all indexes (sys.indexes) and then links to their columns and checks to see which columns are designed as included columns (ic.is_included_column = 1) and then lists out all those indexes and all those columns.

sys.indexes

ic.is_included_column = 1

Copied from http://www.midnightdba.com/Jen/2009/12/get-index-included-column-info/

SELECT
OBJECT_NAME(i.[object_id]) TableName ,
i.[name] IndexName ,
c.[name] ColumnName ,
ic.is_included_column ,
i.index_id ,
i.type_desc ,
i.is_unique ,
i.data_space_id ,
i.ignore_dup_key ,
i.is_primary_key ,
i.is_unique_constraint
FROM
sys.indexes i
JOIN sys.index_columns ic ON ic.object_id = i.object_id and i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
ORDER BY
tableName ,
ic.index_id ,
ic.index_column_id


If you want a globally system stored procedure available to get index keys/nonkeys(include) columns then run the following code

USE master
GO

CREATE PROCEDURE sp_helpcolindex
@objname NVARCHAR(776)
AS
BEGIN
SELECT
c.Name,
CASE ic.is_included_column WHEN 0 THEN 'Key' ELSE 'Include' END AS [Type]
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.name = @objname
ORDER BY [Type] DESC
END
GO

EXEC sp_ms_marksystemobject 'sp_helpcolindex'
GO


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.