I’m from the age when sp_help and it’s variants could give to us almost any information about the database objects.<\/p>\n
Today the server evolved, we have a lot of powerful DMVs to get all the information we would like. The sp_help procs are still there but sometimes they can’t give to us even some basic information.<\/p>\n
For example, included columns was created in SQL Server 2005 and sp_help was never updated to give to us information about the included columns in an index.<\/p>\n
Because such lack of information that DBAs needs to use DMVs to retrieve even basic information such as the included columns of an index. Of course for a DBA it’s easy to create this kind of query, but when you need the information quickly it’s unpleasant need to remember all the joins, keys and fields of the DMV’s to retrieve the information.<\/p>\n
Because this DBAs needed to create a toolset of functions and procs that help in these situations.<\/p>\n
For example, the included columns problem. We will need this information whenever we need to check the index or even drop and re-create the index, so let’s see a query to retrive this information:<\/p>\n
with Columnsqry as \n (select name,ic.object_id,ic.index_id,is_included_column,ic.key_ordinal\n from sys.index_columns IC,\n sys.columns c\n where ic.object_id=c.object_id and \n ic.column_id = c.column_id ), \n IndexQry as\n (select I.object_id,I.index_id, \n (select stuff((select ',' + name as [text()] from Columnsqry q\n where q.object_id=I.object_id\n and q.index_id=i.index_id and q.is_included_column=0\n order by q.key_ordinal\n for xml path('')),1,1,'')) Keys,\n (select stuff((select ',' + name as [text()] from Columnsqry q\n where q.object_id=I.object_id\n and q.index_id=i.index_id and q.is_included_column=1\n for xml path('')),1,1,'')) Included \n from Columnsqry q, sys.indexes I, \n sys.objects o\n where q.object_id=I.object_id\n and q.index_id=i.index_id \n and o.object_id=I.object_id \n and O.type not in ('S','IT')\n group by I.object_id,I.index_id)\nselect IQ.object_id,o.name as [table],\n IQ.Index_id,I.name as [Index],\n I.type_desc,\n keys,included,\n is_unique,fill_factor,is_padded,\n has_filter,filter_definition\nfrom IndexQry IQ, Sys.objects o,sys.indexes I\nwhere IQ.object_id=o.object_id \n and IQ.object_id=I.object_id \n and IQ.Index_id=I.index_id<\/pre>\n<\/p>\nThe information retrieved by this query are quite complete, it retrieves the index keys and included keys in their order, but the query is far from simple: beside all the joins, grouping and CTE’s, it uses an interesting trick with ‘for xml path’ to concatenate key names. This complexity prove my point: DBA’s needs a toolset with procs and functions to do tasks like this.<\/p>\n
It’s easy to create a table value function for this query, so this is a good function to include in your toolset.<\/p>\n
-- =============================================\n-- Author: Dennes Torres\n-- Create date: 29\/03\/2015\n-- Description: Fornece informações mais completas sobre\n-- indices, com suas chaves, included columns\n-- e outras informações\n-- =============================================\nCREATE FUNCTION IndexInformation()\nRETURNS TABLE \nAS\nRETURN \n(\nwith Columnsqry as (\nselect name,ic.object_id,ic.index_id,is_included_column,ic.key_ordinal\nfrom sys.index_columns IC,\n sys.columns c\n where ic.object_id=c.object_id and \n ic.column_id = c.column_id ), IndexQry as\n (select I.object_id,I.index_id, (select stuff((select ',' + name as [text()] from Columnsqry q\n where q.object_id=I.object_id\n and q.index_id=i.index_id and q.is_included_column=0\n order by q.key_ordinal\n for xml path('')),1,1,'')) Keys,\n (select stuff((select ',' + name as [text()] from Columnsqry q\n where q.object_id=I.object_id\n and q.index_id=i.index_id and q.is_included_column=1\n for xml path('')),1,1,'')) Included \n from Columnsqry q, sys.indexes I, sys.objects o\n where q.object_id=I.object_id\n and q.index_id=i.index_id and o.object_id=I.object_id and O.type not in ('S','IT')\n group by I.object_id,I.index_id)\n select IQ.object_id,o.name as [table],\n IQ.Index_id,I.name as [Index],\n I.type_desc,\n keys,included,\n is_unique,fill_factor,is_padded,\n has_filter,filter_definition\n from IndexQry IQ, Sys.objects o,sys.indexes I\n where IQ.object_id=o.object_id \n and IQ.object_id=I.object_id \n and IQ.Index_id=I.index_id\n)\nGO<\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"I’m from the age when sp_help and it’s variants could give to us almost any information about the database objects. Today the server evolved, we have a lot of powerful DMVs to get all the information we would like. The sp_help procs are still there but sometimes they can’t give to us even some basic……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-7855","post","type-post","status-publish","format-standard","hentry","category-other"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7855","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=7855"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7855\/revisions"}],"predecessor-version":[{"id":42475,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7855\/revisions\/42475"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=7855"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=7855"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=7855"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=7855"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}