{"id":476,"date":"2013-02-26T13:29:28","date_gmt":"2013-02-26T20:29:28","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=476"},"modified":"2013-02-25T14:00:47","modified_gmt":"2013-02-25T21:00:47","slug":"sql-query-list-all-indexes-and-their-columns","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2013\/02\/sql-query-list-all-indexes-and-their-columns\/","title":{"rendered":"SQL Query: List All Indexes And Their Columns"},"content":{"rendered":"<p>Looking to write a SQL query which will list out all the indexes in your database as well as the columns in the index? Well my friend you've come to the right place. The query below will give list out the following information:<\/p>\n<p>Schema<br \/>\nTable Name (or view)<br \/>\nIndex Name<br \/>\nIs Primary Key<br \/>\nIs Unique Index<br \/>\nColumn Name<br \/>\nIs Included Column<br \/>\nColumn Order<\/p>\n<p>The results will look something like this (click the image for a larger view):<\/p>\n<p style=\"text-align: center;\"><a href=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/Query-All-Indexes-And-Columns.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter  wp-image-477\" alt=\"Query All Indexes And Columns\" src=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/Query-All-Indexes-And-Columns.png\" width=\"537\" height=\"267\" srcset=\"http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/Query-All-Indexes-And-Columns.png 895w, http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/Query-All-Indexes-And-Columns-300x149.png 300w\" sizes=\"auto, (max-width: 537px) 100vw, 537px\" \/><\/a><\/p>\n<p>Run the query and give it a go...<\/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\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>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Looking to write a SQL query which will list out all the indexes in your database as well as the columns in the index? Well my friend you've come to the right place. The query below will give list out the following information: Schema Table Name (or view) Index Name Is Primary Key Is Unique [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,86,43,3,57,72],"tags":[13,10,9,12,11,54,173,5],"class_list":["post-476","post","type-post","status-publish","format-standard","hentry","category-data-architect","category-dba","category-microsoft-sql-server","category-scripts","category-t-sql","category-table-design","tag-indexes","tag-ms-sql","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-ssms","tag-t-sql","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/476","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=476"}],"version-history":[{"count":2,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/476\/revisions"}],"predecessor-version":[{"id":480,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/476\/revisions\/480"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=476"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=476"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=476"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}