DataWriter

March 16, 2011

Listing Index Columns

Filed under: SQLServerPedia Syndication,T-SQL — Joe Casella @ 9:58 pm
Tags: , ,

I was documenting a database this afternoon and needed a list of indexed tables and columns.  Since sp_helpindex doesn’t list included or filtered index columns, I created my own version using DMVs:

  1.   SELECT Table_Name       =SCHEMA_NAME(st.Schema_id) + '.'
  2.                          + OBJECT_NAME(st.object_id)
  3.      , Index_Name       = si.name
  4.      , Index_type       = LOWER(si.Type_Desc)
  5.                           + CASE WHEN si.is_unique = 1 THEN ', unique'
  6.                                  ELSE '' END
  7.                           + CASE WHEN si.is_primary_key = 1 THEN ', primary key'
  8.                                  ELSE '' END
  9.      , Indexed_Columns  = LEFT(ixColumns, LEN(ixColumns) 1)
  10.      , Included_Columns = LEFT(includedColumns, LEN(includedColumns) 1)
  11.      , si.filter_definition
  12.   FROM sys.tables st
  13.   join sys.indexes si
  14.     on st.object_id = si.object_id
  15. CROSS APPLY (
  16.             SELECT sc.Name + CASE WHEN is_descending_key = 1 THEN ' DESC'
  17.                                   ELSE '' END
  18.                  + ', '
  19.               FROM sys.index_columns ic
  20.               JOIN sys.columns sc
  21.                 on ic.object_id = sc.object_id
  22.                AND ic.column_id = sc.column_id
  23.              WHERE si.object_id = ic.object_id
  24.                AND is_included_column = 0
  25.                and si.index_id = ic.index_id
  26.              ORDER BY ic.Key_Ordinal
  27.               FOR XML PATH('') ) ix (ixColumns)
  28. CROSS APPLY (
  29.             SELECT sc2.Name + ', '
  30.               FROM sys.index_columns ic2 JOIN sys.columns sc2 on ic2.object_id = sc2.object_id
  31.                AND ic2.column_id = sc2.column_id
  32.              WHERE si.object_id = ic2.object_id
  33.                AND is_included_column = 1
  34.                and si.index_id = ic2.index_id
  35.              ORDER BY ic2.Key_Ordinal
  36.               FOR XML PATH('') ) nc (includedColumns);

I like using sys.tables instead of sys.objects whenever I can because there’s no need to filter out the system objects.  If your database also includes indexed views, however, you’ll want to use sys.objects instead.

Advertisements

3 Comments »

  1. […] DMVs. In two earlier posts, I showed how to query DMVs to produce list of the columns in an index or table […]

    Pingback by T-SQL Tuesday #17–APPLY « DataReader — April 12, 2011 @ 11:07 pm | Reply

  2. Why not use sys.views for views, instead of sys.objects?

    Comment by Fred — April 13, 2011 @ 12:16 pm | Reply

    • Looks like my last sentence was a little ambiguous. You’re right, of course, I you can use sys.views to list(only)the views. To list views and tables in the same results set, you’d want to use sys.objects.

      Comment by Joe Casella — May 4, 2011 @ 10:00 pm | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: