Menu
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)?
694
Indexes with included columns provide the greatest benefit when covering the query. This means that the index includes all columns referenced by your query, as you can add columns with data types, number or size not allowed as index key columns.
You can use the sp_helpindex to view all the indexes of one table. And for all the indexes, you can traverse sys. objects to get all the indexes for each table. Only problem with this is that it only includes the index key columns, not the included columns.
To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA. STATISTICS WHERE TABLE_SCHEMA = 'your_schema'; Removing the where clause will show you all indexes in all schemas.
As per my understanding the leaf level of the clustered index are the actual data pages itself, and it stores entire row include with the key column (on which index is created). By definition a clustered index includes all columns... so there are none left to include.
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.
63
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 love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
