{"id":75,"date":"2023-02-09T20:19:03","date_gmt":"2023-02-09T18:19:03","guid":{"rendered":"http:\/\/130.61.57.200\/?p=75"},"modified":"2023-07-30T02:22:21","modified_gmt":"2023-07-30T00:22:21","slug":"find-all-indexes-on-a-table","status":"publish","type":"post","link":"http:\/\/130.61.57.200\/index.php\/2023\/02\/09\/find-all-indexes-on-a-table\/","title":{"rendered":"Find all indexes on a table"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">It is useful to be able to see all indexes on a table. There is the shipped stored procedure <strong>sp_helpindex<\/strong> but the following query can reveal some more inforamtion such as the Included and Filter columns.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Declare @table varchar(255) = &#039;MyTable&#039;, @schema varchar(50) =&#039;dbo&#039;;\nwith IDX as \n(\nSELECT schema_name(o.schema_id) as &#091;SCHEMA],O.NAME AS &#091;TABLE],AC.NAME AS &#091;COLUMN],\nI.name AS &#091;INDEX],I.type_desc AS &#091;TYPE],  IC.is_included_column AS &#091;INCLUDED],\nisnull(I.filter_definition,&#039;N\/A&#039;) AS &#091;FILTER],I.is_disabled as &#091;DISABLED],allow_page_locks as &#091;PAGE LOCKS], allow_row_locks as &#091;ROW LOCKS]\nFROM SYS.all_columns AC JOIN SYS.objects O ON AC.object_id=O.object_id  \nJOIN SYS.index_columns IC ON IC.object_id=O.object_id AND AC.column_id=IC.column_id  \nJOIN SYS.indexes I ON I.index_id=IC.index_id AND I.object_id=O.object_id   \nWHERE O.NAME =@table and schema_name(o.schema_id)=@schema\n)  \nselect &#091;SCHEMA],&#091;TABLE],&#091;INDEX],&#091;TYPE],\nstuff((select &#039;,&#039;+&#091;COLUMN] from IDX ii \nwhere ii.&#091;TABLE]=i.&#091;TABLE] and ii.&#091;INDEX]=i.&#091;INDEX] and ii.INCLUDED=0  \nfor xml path (&#039;&#039;)),1,1,&#039;&#039;) as &#091;COLUMNS],  \nisnull(stuff((select &#039;,&#039;+&#091;COLUMN] from IDX ii where ii.&#091;TABLE]=i.&#091;TABLE] and ii.&#091;INDEX]=i.&#091;INDEX] and ii.INCLUDED=1  for xml path (&#039;&#039;)),1,1,&#039;&#039;),&#039;N\/A&#039;) as &#091;INCLUDED],&#091;FILTER],&#091;DISABLED] ,&#091;ROW LOCKS],&#091;PAGE LOCKS] \nfrom idx i  \ngroup by &#091;SCHEMA],&#091;TABLE],&#091;INDEX],&#091;TYPE],&#091;FILTER],&#091;DISABLED],&#091;ROW LOCKS],&#091;PAGE LOCKS] <\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>It is useful to be able to see all indexes on a table. There is the shipped stored procedure sp_helpindex but the following query can reveal some more inforamtion such as the Included and Filter columns.<\/p>\n","protected":false},"author":1,"featured_media":104,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[37,35],"class_list":["post-75","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mssql","tag-filtered-index","tag-index"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/75","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=75"}],"version-history":[{"count":1,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/75\/revisions"}],"predecessor-version":[{"id":76,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/75\/revisions\/76"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/104"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=75"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=75"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=75"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}