Mickey's T-SQL Ponderings

First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Through the Looking Glass I see Many Indexes

Last year, I was doing quite a bit of query tuning. I was constantly looking at what indexes existed before I made new ones.  I created this snippet to Red Gates’ SQL Prompt to be able to quickly look at all the indexes for a given table. Sometimes I even found duplicate indexes when uses this snippet. It’s one of my favorites. I call it Indexes4Table.

--Use Shift + Ctrl + M to set Template Placeholders
USE <database,sysname,$dbname$>
GO
/* ***************************************************
Purpose:    Lists all indexes for a table
Author:     mickey stuewe www.mickeystuewe.com
Date:       12/6/2013
*************************************************** */
SELECT
	i.name AS IndexName
	,i.type_desc
	,STUFF(Indexed.ColumnList, 1, 1, '') AS IndexColumnList
	,STUFF(Included.ColumnList, 1, 1, '') AS IncludeColumnList
	,i.filter_definition
	,i.fill_factor
FROM
	sys.indexes AS i
	OUTER APPLY
			(
				SELECT
					',' +  CASE 
							WHEN ic.is_descending_key = 1 THEN col.name + ' DESC'
							ELSE col.name  
							END 
				FROM
					sys.index_columns AS ic 
					INNER JOIN sys.columns col ON ic.object_id = col.object_id
													AND ic.column_id = col.column_id
				WHERE
					ic.index_id = i.index_id
					AND ic.object_id =  i.object_id
					AND ic.is_included_column = 0
				ORDER BY
					ic.index_column_id
				FOR XML	PATH('')
			) AS Indexed(ColumnList)
	OUTER APPLY
			(
				SELECT
					',' +  CASE 
							WHEN ic.is_descending_key = 1 THEN col.name + ' DESC'
							ELSE col.name  
							END 
				FROM
					sys.index_columns AS ic 
					INNER JOIN sys.columns col ON ic.object_id = col.object_id
													AND ic.column_id = col.column_id
				WHERE
					ic.index_id = i.index_id
					AND ic.object_id =  i.object_id
					AND ic.is_included_column = 1
				ORDER BY
					ic.index_column_id
				FOR XML	PATH('')
			) AS Included(ColumnList)
WHERE
	i.object_id =  OBJECT_ID(N'<schema_name,sysname,dbo>.<table_name,sysname,>')
ORDER BY
	i.type_desc
	,Indexed.ColumnList

There are a couple of things I want you to notice. I’m using a string concatenation pattern based on Peter Larson’s (b) SQL, which is written about on Adam Machanic’s (b|t) blog here. I also wanted you to notice that you can use placeholders within a string. See line 57.

I ran my script against the Persons.Person table in AdventureWorks2008R2 (I also added an index so that you can see all the columns filled out. Here is the outcome.

Now I can quickly look at all the values that I find important for my indexes on a particular table.

Previous Post In Series: SQL Advent Calendar – Day 5 – Snippet For New Indexes

Next Post In Series: SQL Advent Calendar – Day 7 – Verify All Tables and Fields Have a Definition