{"id":470,"date":"2013-02-28T09:00:16","date_gmt":"2013-02-28T16:00:16","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=470"},"modified":"2013-02-25T14:06:52","modified_gmt":"2013-02-25T21:06:52","slug":"sql-query-to-find-which-columns-an-index-is-on","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2013\/02\/sql-query-to-find-which-columns-an-index-is-on\/","title":{"rendered":"SQL Query: To Find Which Columns An Index Is On"},"content":{"rendered":"<p>Do you need to write a SQL Server query to find out which columns an index is on? Do you have an index name but need to use T-SQL to determine the column names? Not a problem! The query below will qive you exactly what you want. The example uses an AdventureWorks index as the example so just replace it with your own index name and you'll be in business.<\/p>\n<p><em>Note: Need a query to show you all the indexes and columns in your database? You can hack the query below or just go to this post where I did the hacking for you: <a href=\"http:\/\/www.sqldbpros.com\/2013\/02\/sql-query-list-all-indexes-and-their-columns\/\">SQL Query: List All Indexes And Their Columns<\/a><\/em><\/p>\n<pre>SELECT\u00a0 OBJECT_SCHEMA_NAME(ind.object_id) AS SchemaName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , OBJECT_NAME(ind.object_id) AS ObjectName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , ind.name AS IndexName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , ind.is_primary_key AS IsPrimaryKey\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , ind.is_unique AS IsUniqueIndex\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , col.name AS ColumnName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , ic.is_included_column AS IsIncludedColumn\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , ic.key_ordinal AS ColumnOrder\r\nFROM\u00a0\u00a0\u00a0 sys.indexes ind\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.index_columns ic\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON ind.object_id = ic.object_id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND ind.index_id = ic.index_id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.columns col\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON ic.object_id = col.object_id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND ic.column_id = col.column_id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.tables t\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON ind.object_id = t.object_id\r\nWHERE\u00a0\u00a0 t.is_ms_shipped = 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND ind.name = 'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode' --your index name here\r\nORDER BY OBJECT_SCHEMA_NAME(ind.object_id) --SchemaName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , OBJECT_NAME(ind.object_id) --ObjectName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , ind.is_primary_key DESC\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , ind.is_unique DESC\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , ind.name --IndexName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , ic.key_ordinal<\/pre>\n<p>The result set will look something like this (click the image if you need a larger view):<\/p>\n<p style=\"text-align: center;\"><a href=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/columns-in-a-SQL-index.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter  wp-image-471\" alt=\"columns in a SQL index\" src=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/columns-in-a-SQL-index.png\" width=\"545\" height=\"88\" srcset=\"http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/columns-in-a-SQL-index.png 909w, http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/columns-in-a-SQL-index-300x48.png 300w\" sizes=\"auto, (max-width: 545px) 100vw, 545px\" \/><\/a><\/p>\n<p>That should do the trick!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Do you need to write a SQL Server query to find out which columns an index is on? Do you have an index name but need to use T-SQL to determine the column names? Not a problem! The query below will qive you exactly what you want. The example uses an AdventureWorks index as the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[86,43,57],"tags":[13,10,6,9,12,11,173,5],"class_list":["post-470","post","type-post","status-publish","format-standard","hentry","category-dba","category-microsoft-sql-server","category-t-sql","tag-indexes","tag-ms-sql","tag-script","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-t-sql","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=470"}],"version-history":[{"count":7,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/470\/revisions"}],"predecessor-version":[{"id":482,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/470\/revisions\/482"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=470"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}