— 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.
Ahhh…the good old days when SQL really started coming out of its shell.
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,
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
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!