T-SQL Tuesday #104: Index column script

[fusion_builder_container hundred_percent=”no” hundred_percent_height=”no” hundred_percent_height_scroll=”no” hundred_percent_height_center_content=”yes” equal_height_columns=”no” menu_anchor=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”center center” background_repeat=”no-repeat” fade=”no” background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ video_mp4=”” video_webm=”” video_ogv=”” video_url=”” video_aspect_ratio=”16:9″ video_loop=”yes” video_mute=”yes” video_preview_image=”” border_size=”” border_color=”” border_style=”solid” margin_top=”” margin_bottom=”” padding_top=”” padding_right=”” padding_bottom=”” padding_left=””][fusion_builder_row][fusion_builder_column type=”2_3″ layout=”2_3″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” dimension_margin=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” class=”” id=””]

Introduction

Bert Wagner (blog) is hosting this month’s T-SQL Tuesday, and the topic is code that you’ve written that you’d hate to live without.

I’ve written scripts for myself that I keep in my folder of Toolbox scripts, so I had a few choices. I decided to deliver a script that I find myself using almost daily. This script was written to solve a specific problem: There’s no good way to display indexes and their columns, built into SQL Server.

[/fusion_text][/fusion_builder_column][fusion_builder_column type=”1_3″ layout=”1_3″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” dimension_margin=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_imageframe image_id=”11995″ max_width=”” style_type=”none” stylecolor=”” hover_type=”none” bordersize=”” bordercolor=”” borderradius=”” align=”none” lightbox=”no” gallery_id=”” lightbox_image=”” alt=”” link=”https://bertwagner.com/2018/07/03/code-youd-hate-to-live-without-t-sql-tuesday-104-invitation/” linktarget=”_self” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=””]https://www.dba-art.com/wp-content/uploads/2017/11/T-SQL-Tuesday-Logo.jpg[/fusion_imageframe][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container][fusion_builder_container hundred_percent=”no” hundred_percent_height=”no” hundred_percent_height_scroll=”no” hundred_percent_height_center_content=”yes” equal_height_columns=”no” menu_anchor=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”center center” background_repeat=”no-repeat” fade=”no” background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ video_mp4=”” video_webm=”” video_ogv=”” video_url=”” video_aspect_ratio=”16:9″ video_loop=”yes” video_mute=”yes” video_preview_image=”” border_size=”” border_color=”” border_style=”solid” margin_top=”” margin_bottom=”” padding_top=”” padding_right=”” padding_bottom=”” padding_left=””][fusion_builder_row][fusion_builder_column type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” dimension_margin=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” class=”” id=””]

Requirements to run this script

You must be on SQL Server 2008 and above. Sorry, this script relies on the FOR XML PATH clause. You need access to the DMVs sys.indexes and sys.index_columns, and you must be using the right database context, i.e. USE [StackOverflow].

Here’s the script

[fusion_syntax_highlighter theme=”” language=”sql” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”Copy to Clipboard” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_left=”” margin_bottom=”” margin_right=””]U0VMRUNUIGkubmFtZSBhcyBJbmRleF9OYW1lCiAgICAsT0JKRUNUX05BTUUoaS5vYmplY3RfaWQpIGFzIFRhYmxlX05hbWUKICAgICxpLnR5cGVfZGVzYyBhcyBJbmRleF9UeXBlCiAgICAsU1RVRkYoKAogICAgICAgIFNFTEVDVCAnLCcgKyBDT0xfTkFNRShpYy5vYmplY3RfaWQsIGljLmNvbHVtbl9pZCkKICAgICAgICBGUk9NIHN5cy5pbmRleGVzIGFzIGluYwogICAgICAgIElOTkVSIEpPSU4gc3lzLmluZGV4X2NvbHVtbnMgYXMgaWMgb24gaW5jLm9iamVjdF9pZCA9IGljLm9iamVjdF9pZAogICAgICAgICAgICBhbmQgaW5jLmluZGV4X2lkID0gaWMuaW5kZXhfaWQKICAgICAgICBXSEVSRSBpc19pbmNsdWRlZF9jb2x1bW4gPSAwCiAgICAgICAgICAgIGFuZCBpbmMub2JqZWN0X2lkID0gaS5vYmplY3RfaWQKICAgICAgICAgICAgYW5kIGluYy5pbmRleF9pZCA9IGkuaW5kZXhfaWQKICAgICAgICBPUkRFUiBCWSBrZXlfb3JkaW5hbAogICAgICAgIEZPUiBYTUwgUEFUSCgnJykKICAgICAgICApLCAxLCAxLCAnJykgQVMgS2V5X1ZhbHVlcwogICAgLFNUVUZGKCgKICAgICAgICBTRUxFQ1QgJywnICsgQ09MX05BTUUoaWMub2JqZWN0X2lkLCBpYy5jb2x1bW5faWQpCiAgICAgICAgRlJPTSBzeXMuaW5kZXhlcyBhcyBpbmMKICAgICAgICBJTk5FUiBKT0lOIHN5cy5pbmRleF9jb2x1bW5zIGFzIGljIG9uIGluYy5vYmplY3RfaWQgPSBpYy5vYmplY3RfaWQKICAgICAgICAgICAgYW5kIGluYy5pbmRleF9pZCA9IGljLmluZGV4X2lkCiAgICAgICAgV0hFUkUgaXNfaW5jbHVkZWRfY29sdW1uID0gMQogICAgICAgICAgICBhbmQgaW5jLm9iamVjdF9pZCA9IGkub2JqZWN0X2lkCiAgICAgICAgICAgIGFuZCBpbmMuaW5kZXhfaWQgPSBpLmluZGV4X2lkCiAgICAgICAgT1JERVIgQlkga2V5X29yZGluYWwKICAgICAgICBGT1IgWE1MIFBBVEgoJycpCiAgICAgICAgKSwgMSwgMSwgJycpIEFTIEluY2x1ZGVkX0NvbHVtbnMKICAgIEZST00gc3lzLmluZGV4ZXMgYXMgaQogICAgV0hFUkUgb2JqZWN0X25hbWUoaS5vYmplY3RfaWQpID0gJ1Bvc3RzJwo=[/fusion_syntax_highlighter]

Results

Explanations and DMVs

So why is this script so useful to me? Well, I deal with tables that have wide indexes with many columns. In the picture above, I’m looking at the IX_ViewCount index. If I queried just the DMVs without the extra formatting, there would be five rows, one for each column.

Limitations

I use this script for Clustered and Nonclustered indexes. I haven’t tested it with more complex index types, like Columnstore or In-Memory, so there could be limitations there. I also didn’t include index size in pages or bytes, but it wouldn’t be too difficult to add.

[/fusion_text][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

Leave a Reply

Your email address will not be published. Required fields are marked *

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close