BI SQL # 113 : SQL Server DBA Scripts : Display Table, Index Name , Key Cols and list of included columns
Hi Geeks,
In this article we are going to cover How to Display Table, Index Name , Key Cols and list of included columns
In this post we are going to discuss following points:
- Problem Statement of SQL Script:
- Description of SQL Script:
- SQL Script Output Column
- SQL Script Code
- User Level to execute
- SQL Script Output Screenshot
Problem Statement of SQL Script:
How to Display List of Table, Index Name, Key Cols and list of included cols in one result?
Description of SQL Script:
This script will display, Index Name, Key Cols and list of included cols
SQL Script Output Column
SQL Script Code
SELECT '[' + Sch.NAME + '].[' + Tab.[name] + ']' AS TableName ,Ind.[name] AS IndexName ,SUBSTRING(( SELECT ', ' + AC.NAME FROM sys.[tables] AS T INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id] INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC. [object_id] AND I.[index_id] = IC.[index_id] INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id AND IC.is_included_column = 0 ORDER BY IC.key_ordinal FOR XML PATH('') ), 2, 8000) AS KeyCols ,SUBSTRING(( SELECT ', ' + AC.NAME FROM sys.[tables] AS T INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id] INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC. [object_id] AND I.[index_id] = IC.[index_id] INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id AND IC.is_included_column = 1 ORDER BY IC.key_ordinal FOR XML PATH('') ), 2, 8000) AS IncludeCols FROM sys.[indexes] Ind INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id] INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id] -- WHERE Tab.name = 'MyTableNameHere'
-- uncomment to get single table indexes detail ORDER BY TableName
SQL Script Output Screenshot
User Level to execute
- 300
Hope you will like to Display Table, Index Name , Key Cols and list of included columns.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Infographic Mentalist >> Image worth explaining thousand Words
Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ
DBA Mentalist >>Advance SQL Server Blog
Microsoft BI Mentalist >> MS BI Development Update
Connect With me on
SQL Server Mentalist 
I will Copy this Script to my blog because it;s very Useful and i make Library to Scripts because i Need it on my Work
Good Work
Sure Motfa ! Just use my link if you want !
Thanks