List Index Columns

By Lori Brown | Beginner

Jul 08

— By Lori Brown @SQLSupahStah

I was looking for a way to get info on the columns in indexes so that I could use the data to help enhance my index defrag routine. I found that my defrag logic did not figure out if an index has included columns that are LOBs and was failing because it was trying to rebuild it ONLINE. Of course this was happening on a SQL 2008 R2 Enterprise instance. Like it or not, I have a lot of clients with older versions of SQL that I support so I try to account for things but LOBs in an included column had been missed.Blog_20160708_1

Ahhh…the good old days when SQL really started coming out of its shell.

Blog_20160708_2

Now we have new and more powerful SQL 2016. Progress!!

Anyway, I decided that it would be nice to have a query that would list index columns, complete with data type and in key order along with a way to tell if it is an included column. I added some ways to filter the query that I think are common ways to look at this type of data.

— Returns index column info for the database you are connected to

SELECT s.name AS SchemaName,

o.name AS TableName,

i.name AS IndexName,

i.type_desc IndexType ,

c.name AS ColName,

t.name AS ColType,

ic.index_column_id,

ic.is_included_column

FROM sys.objects o

JOIN sys.schemas s ON (o.schema_id = s.schema_id)

JOIN sys.indexes i ON (o.object_id = i.object_id)

JOIN sys.index_columns ic ON (i.object_id = ic.object_id AND i.index_id = ic.index_id)

JOIN sys.columns c ON (i.object_id = c.object_id AND c.column_id = ic.column_id)

JOIN sys.types t ON (c.user_type_id = t.user_type_id)

WHERE o.type = ‘U’

–AND o.name = ‘???’ — Uncomment to filter by table name

–AND o.object_id = 123456789 — Uncomment to filter by object id

–AND (t.name IN (‘text’, ‘ntext’, ‘image’) OR t.max_length = -1) — Uncomment this line for LOBs only

ORDER BY o.name, i.name, c.name, ic.index_column_id

Blog_20160708_3

Even though I know that others have posted similar queries, I like to make my own. This works on SQL 2008 through SQL 2016. I hope someone finds this useful. Let me know of ways to improve it too!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>