[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]