Home » Uncategorized » Currently Reading:

Get Index Included Column Info

December 17, 2009 Uncategorized 2 Comments

Content rating: Beginner, tip

One of my pet peeves with SQL is that there isn't a quick and easy way to get all the information about indexes. Sure, you have sp_help tablename or sp_helpindex tablename to get the index name and keys for a table, but that's not even half the story.  I use a lot of indexes with INCLUDE – more on that in the next blog – and INCLUDEd columns don't show up in sp_help and sp_helpindex.

To get around this, you can keep this query on hand, or just turn it into a view:

SELECT OBJECT_NAME(i.[object_id]) TableName ,

i.[name] IndexName ,

c.[name] ColumnName ,

ic.is_included_column ,

i.index_id ,

i.type_desc ,

i.is_unique ,

i.data_space_id ,

i.ignore_dup_key ,

i.is_primary_key ,

i.is_unique_constraint

FROM sys.indexes i

JOIN sys.index_columns ic ON ic.object_id = i.object_id

AND i.index_id = ic.index_id

JOIN sys.columns c ON ic.object_id = c.object_id

AND ic.column_id = c.column_id

ORDER BY tableName ,

ic.index_id ,

ic.index_column_id

That'll give you a lovely bit of information: the table, index, and column names, along with all your day to day index info needs.  Slap a WHERE i.[name] = 'indexname' in there, and you get information for a particular index.

Jen McCown

http://www.MidnightDBA.com

Currently there are "2 comments" on this Article:

  1. [...] This post was mentioned on Twitter by Wendy Pastrick, Jen & Sean McCown. Jen & Sean McCown said: SQL Awesomesauce from the vault: Get Index Included Column Info http://tinyurl.com/2cp92ub INCLUDEd columns don't show up in sp_help and… [...]

  2. [...] while back, I wrote a simple little query that lets you Get Index Included Column Info (because sp_help and sp_helpindex only show non-included index columns).  More recently, [...]

Comment on this Article:







Release Date: Minion by MidnightDBA

Just 2 days 23 hours 39 minutes 20 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/