Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by (8Microsoft Community Contributo)
  • When: 6 Sep 2013 11:01 AM
  • Last revision by (8Microsoft Community Contributo)
  • When: 8 Sep 2013 4:02 AM
  • Revisions: 7
  • Comments: 3
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  T-SQL Script to Get Detailed Information about Index Settings

T-SQL Script to Get Detailed Information about Index Settings

T-SQL Script to Get Detailed Information about Index Settings

This article is about the script which I wrote to get detailed information about Index settings. The script in this article does not show any information about missing indexes or index usage details, the script will show only the information about settings made on an Index using CREATE /ALTER INDEX  statement.

Example:

Just for demo of the script, we will make use of table Person.Address from AdventureWorks database.

Using system stored procedures SP_HELP and SP_HELPINDEX, we can get only the index_name, index_description and index_keys details.

USE AdventureWorks2012
GO
sp_help 'Person.Address'
GO
sp_helpindex 'Person.Address'
GO



Just for testing purpose I am going to create NONCLUSTERED Filtered index with included columns and then alter the fill factor of the created index .

USE AdventureWorks2012 
GO 
    
CREATE NONCLUSTERED INDEX IX_Address_PostalCode 
ON Person.Address (PostalCode DESC ) 
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID) 
WHERE City = 'Seattle'
GO 
    
ALTER INDEX IX_Address_PostalCode ON Person.Address 
REBUILD WITH (FILLFACTOR = 80);

Below code block will get us the information about settings made on an Index using CREATE /ALTER INDEX  statement:

USE AdventureWorks2012 
GO 
    
SELECT 
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END [Is_unique], 
    I.type_desc+' INDEX' IndexType, 
    I.name IndexName, 
    Schema_name(T.Schema_id)+'.'+T.name ObjectName,
    KeyColumns, 
    IncludedColumns, 
    I.Filter_definition, 
    CASE WHEN I.is_padded = 1 THEN ' ON ' ELSE ' OFF ' END [PAD_INDEX], 
    I.Fill_factor, 
    ' OFF ' [SORT_IN_TEMPDB] , -- default value  
    CASE WHEN I.ignore_dup_key = 1 THEN ' ON ' ELSE ' OFF ' END [Ignore_dup_key], 
    CASE WHEN ST.no_recompute = 0 THEN ' OFF ' ELSE ' ON ' END [Stats_Recompute], 
    ' OFF ' [DROP_EXISTING] ,-- default value  
    ' OFF ' [ONLINE] , -- default value  
    CASE WHEN I.allow_row_locks = 1 THEN ' ON ' ELSE ' OFF ' END [Allow_row_locks], 
    CASE WHEN I.allow_page_locks = 1 THEN ' ON ' ELSE ' OFF ' END [Allow_page_locks] ,  
    CASE WHEN ST.auto_created = 0 THEN ' Not Automatically Created ' ELSE ' Automatically Created ' END [Statistics_Creation], 
    CASE WHEN I.is_primary_key = 1 THEN 'Yes' ELSE 'NO' END 'Part of PrimaryKey', 
    CASE WHEN I.is_unique_constraint = 1 THEN 'Yes' ELSE 'NO' END 'Part of UniqueKey', 
    CASE WHEN I.is_disabled = 1 THEN 'Disabled' ELSE 'Enabled' END IndexStatus, 
    CASE WHEN I.Is_hypothetical = 1 THEN 'Yes' ELSE 'NO' END Is_hypothetical, 
    CASE WHEN I.has_filter = 1 THEN 'Yes' ELSE 'NO' END 'Filtered Index', 
    DS.name [FilegroupName] 
FROM sys.indexes I 
 JOIN sys.tables T ON T.Object_id = I.Object_id  
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid 
 JOIN (SELECT * FROM (
    SELECT IC2.object_id , IC2.index_id ,
        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
    FROM sys.index_columns IC1
    JOIN Sys.columns C 
       ON C.object_id = IC1.object_id 
       AND C.column_id = IC1.column_id 
       AND IC1.is_included_column = 0
    WHERE IC1.object_id = IC2.object_id 
       AND IC1.index_id = IC2.index_id 
    GROUP BY IC1.object_id,C.name,index_id
    ORDER BY MAX(IC1.key_ordinal)
       FOR XML PATH('')), 1, 2, '') KeyColumns 
    FROM sys.index_columns IC2 
    WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id 
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id 
 --JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id 
 LEFT JOIN (SELECT * FROM ( 
    SELECT IC2.object_id , IC2.index_id , 
        STUFF((SELECT ' , ' + C.name 
    FROM sys.index_columns IC1 
    JOIN Sys.columns C  
       ON C.object_id = IC1.object_id  
       AND C.column_id = IC1.column_id  
       AND IC1.is_included_column = 1 
    WHERE IC1.object_id = IC2.object_id  
       AND IC1.index_id = IC2.index_id  
    GROUP BY IC1.object_id,C.name,index_id 
       FOR XML PATH('')), 1, 2, '') IncludedColumns  
   FROM sys.index_columns IC2  
   WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables 
   GROUP BY IC2.object_id ,IC2.index_id) tmp1 
   WHERE IncludedColumns IS NOT NULL ) tmp2  
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id 
WHERE I.Object_id = object_id('Person.Address') --Comment for all tables





Related Reference links:

http://technet.microsoft.com/en-us/library/ms188783.aspx
http://technet.microsoft.com/en-us/library/ms173760.aspx
http://technet.microsoft.com/en-us/library/ms190283.aspx
http://technet.microsoft.com/en-us/library/ms175105.aspx
http://www.microsoft.com/en-in/download/details.aspx?id=722

See also :
http://social.technet.microsoft.com/wiki/contents/articles/17785.sql-server-query-language-transact-sql.aspx
, , , , , [Edit tags]
Leave a Comment
  • Please add 6 and 3 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 6 Sep 2013 11:16 PM

    SathyanarrayananS edited Revision 2. Comment: added image

  • 6 Sep 2013 11:07 PM

    SathyanarrayananS edited Revision 1. Comment: minor modification in the script

  • 6 Sep 2013 2:29 PM

    Maheshkumar S Tiwari edited Original. Comment: corrected typo error ,added tags and minor edit.

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 6 Sep 2013 2:29 PM

    Maheshkumar S Tiwari edited Original. Comment: corrected typo error ,added tags and minor edit.

  • Posted by
    on 6 Sep 2013 11:07 PM

    SathyanarrayananS edited Revision 1. Comment: minor modification in the script

  • Posted by
    on 6 Sep 2013 11:16 PM

    SathyanarrayananS edited Revision 2. Comment: added image

Page 1 of 1 (3 items)