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 information.

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.

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.

Because this DBAs needed to create a toolset of functions and procs that help in these situations.

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:

The 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.

It’s easy to create a table value function for this query, so this is a good function to include in your toolset.