RSS

Display Table ,Column ,KeyCols ,IncludeCols for All index

04 Jun

Hi Guys

by this Script you can return 

Table Name 

Index Name

Column Name Created on the index

Include Column 

=======================================================

SELECT ‘[‘ + Sch.NAME + ‘].[‘ + Tab.[name] + ‘]’ AS TableName
,Ind.[name] AS IndexName
,SUBSTRING((
SELECT ‘, ‘ + AC.NAME
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.
[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR XML PATH(”)
), 2, 8000) AS KeyCols
,SUBSTRING((
SELECT ‘, ‘ + AC.NAME
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.
[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR XML PATH(”)
), 2, 8000) AS IncludeCols
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]
— WHERE Tab.name = ‘MyTableNameHere’
— uncomment to get single table indexes detail
ORDER BY TableName

 
11 Comments

Posted by on June 4, 2013 in Index, Script

 

11 responses to “Display Table ,Column ,KeyCols ,IncludeCols for All index

  1. http://leparfaitfacts.com

    June 22, 2013 at 8:18 AM

    of course like your web-site however you have to
    check the spelling on quite a few of your posts.

    Many of them are rife with spelling issues and I in finding it very
    bothersome to tell the truth on the other hand I’ll surely come again again.

    http://www.dolina-fragow.pl/profile.php?mode=viewprofile&u=414

     
  2. Eng.Motafa Elmasry

    June 22, 2013 at 5:33 PM

    Thanks I will fix this issue

     
  3. www.google.ca

    July 25, 2014 at 7:03 PM

    This website truly has all of the information I needed concerning
    this subject and didn’t know who to ask.

     
  4. roofing materials types

    July 28, 2014 at 2:41 AM

    What’s Happening i’m new to this, I stumbled upon this I’ve
    discovered It positively useful and it has aided me out loads.

    I hope to contribute & aid different customers like its
    aided me. Great job.

     
  5. saulhardy10.newsvine.com

    August 19, 2014 at 9:12 AM

    Hello! This is my first visit to your blog! We are a group of volunteers and starting a new project in a community in the same niche.
    Your blog provided us valuable information to work on. You have
    done a marvellous job!

     
  6. http://scottiesfqb.blog.com

    September 7, 2014 at 11:08 AM

    I read this paragraph completely about the difference of most recent and
    previous technologies, it’s awesome article.

     
  7. faux slate roofing

    September 7, 2014 at 3:11 PM

    After I originally left a comment I seem to have clicked
    on the -Notify me when new comments are added- checkbox and
    from now on every time a comment is added I get four emails with the exact
    same comment. Is there an easy method you
    can remove me from that service? Cheers!

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s