[SQL Server] 查询索引信息.

红太狼 2022-10-01 00:46 226阅读 0赞

**转自: [http://www.sqlskills.com/BLOGS/KIMBERLY/post/Updates-%28fixes%29-to-sp\_helpindex2.aspx][http_www.sqlskills.com_BLOGS_KIMBERLY_post_Updates-_28fixes_29-to-sp_helpindex2.aspx]**

For SQL Server 2005, here's your new sp\_helpindex2 script:**[sp\_helpindex2\_2005.zip][sp_helpindex2_2005.zip]**(2.89 KB)

And, here's a simple test script for 2005:

DROPTABLE tbl1  
GO

CREATETABLE tbl1( c1 int, c2 int, c3 int, c4 int)  
GO  
CREATEINDEX ix\_1 ON tbl1(c1)INCLUDE(c2)  
CREATEINDEX ix\_2 ON tbl1(c1)  
CREATEINDEX ix\_3 ON tbl1(c1)INCLUDE(c2, c3)  
CREATEINDEX ix\_4 ON tbl1(c1, c3)INCLUDE(c2)  
CREATEINDEX ix\_5 ON tbl1(c3)INCLUDE(c1, c2, c4)  
CREATEINDEX ix\_6 ON tbl1(c1, c2)INCLUDE(c3, c4)  
go

sp\_helpindex2 tbl1  
go

<table style="width: 467px; border-collapse: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm;"> 
 <tbody> 
 <tr style="height: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes;"> 
 <td style="background-color: transparent; width: 63pt; height: 15pt; mso-border-alt: solid windowtext .5pt; border: windowtext 1pt solid; padding: 0cm;"> index_name </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; width: 137pt; height: 15pt; border-top: windowtext 1pt solid; border-right: windowtext 1pt solid; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> index_description </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; width: 58pt; height: 15pt; border-top: windowtext 1pt solid; border-right: windowtext 1pt solid; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> index_keys </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; width: 92pt; height: 15pt; border-top: windowtext 1pt solid; border-right: windowtext 1pt solid; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> included_columns </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 1;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on fg1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2 </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 2;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on fg1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> NULL </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 3;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_3 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on fg1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2, c3 </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 4;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_4 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on fg1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1, c3 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2 </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 5;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_5 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on fg1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c3 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1, c2, c4 </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 6; mso-yfti-lastrow: yes;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_6 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on fg1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1, c2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c3, c4 </td> 
 </tr> 
 </tbody> 
</table>

For SQL Server 2008, here's your new sp\_helpindex2 script: [**sp\_helpindex2\_2008.zip**][sp_helpindex2_2008.zip] (2.84 KB)

And, here's a simple test script for 2008:

DROPTABLE tbl1  
GO

CREATETABLE tbl1( c1 int, c2 int, c3 int, c4 int)  
CREATEINDEX ix\_1 ON tbl1(c1)INCLUDE(c2)  
CREATEINDEX ix\_2 ON tbl1(c1)  
CREATEINDEX ix\_3 ON tbl1(c1)INCLUDE(c2, c3)  
CREATEINDEX ix\_4 ON tbl1(c1, c3)INCLUDE(c2)  
CREATEINDEX ix\_5 ON tbl1(c3)INCLUDE(c1, c2, c4)  
CREATEINDEX ix\_6 ON tbl1(c1, c2)INCLUDE(c3, c4)

CREATEINDEX ix\_1f ON tbl1(c1)INCLUDE(c2)  
WHERE c3 ISNOTNULL  
  
CREATEINDEX ix\_2f ON tbl1(c1)  
WHERE c4 > 2

CREATEINDEX ix\_3f ON tbl1(c1)INCLUDE(c2, c3) 
WHERE c4 > 2 AND c1 < 50 AND c2 = 12

CREATEINDEX ix\_4f ON tbl1(c1, c3)INCLUDE(c2)  
WHERE c4 ISNOTNULLAND c1 = 12

CREATEINDEX ix\_5f ON tbl1(c3)INCLUDE(c1, c2, c4)  
WHERE c1 > 5

CREATEINDEX ix\_6f ON tbl1(c1, c2)INCLUDE(c3, c4) 
WHERE c4 < 20 
go

sp\_helpindex2 tbl1  
go

<table style="width: 752px; border-collapse: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm;"> 
 <tbody> 
 <tr style="height: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes;"> 
 <td style="background-color: transparent; width: 63pt; height: 15pt; mso-border-alt: solid windowtext .5pt; border: windowtext 1pt solid; padding: 0cm;"> index_name </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; width: 166pt; height: 15pt; border-top: windowtext 1pt solid; border-right: windowtext 1pt solid; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> index_description </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; width: 58pt; height: 15pt; border-top: windowtext 1pt solid; border-right: windowtext 1pt solid; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> index_keys </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; width: 92pt; height: 15pt; border-top: windowtext 1pt solid; border-right: windowtext 1pt solid; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> included_columns </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; width: 185pt; height: 15pt; border-top: windowtext 1pt solid; border-right: windowtext 1pt solid; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> filter_definition </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 1;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> NULL </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 2;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_1f </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> ([c3] IS NOT NULL) </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 3;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> NULL </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 4;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_2f </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> ([c4]&gt;(2)) </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 5;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_3 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2, c3 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> NULL </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 6;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_3f </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2, c3 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> ([c4]&gt;(2) AND [c1]&lt;(50) AND [c2]=(12)) </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 7;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_4 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1, c3 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> NULL </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 8;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_4f </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1, c3 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> ([c4] IS NOT NULL AND [c1]=(12)) </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 9;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_5 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c3 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1, c2, c4 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> NULL </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 10;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_5f </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c3 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1, c2, c4 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> ([c1]&gt;(5)) </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 11;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_6 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1, c2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c3, c4 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> NULL </td> 
 </tr> 
 <tr style="height: 15pt; mso-yfti-irow: 12; mso-yfti-lastrow: yes;"> 
 <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm;"> ix_6f </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> nonclustered located on PRIMARY </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c1, c2 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> c3, c4 </td> 
 <td style="border-bottom: windowtext 1pt solid; border-left: #ebe9ed; background-color: transparent; height: 15pt; border-top: #ebe9ed; border-right: windowtext 1pt solid; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm;"> ([c4]&lt;(20)) </td> 
 </tr> 
 </tbody> 
</table>

* [dba\_indexLookup\_sp][dba_indexLookup_sp] – A custom, non-system stored procedure, written by Michelle Ufford
 *  
 
  
 
 IfObjectProperty(Object\_ID('dbo.dba\_indexLookup\_sp'), 
 
         N'IsProcedure')Is Null
 
 Begin
 
     Execute('Create Procedure dbo.dba\_indexLookup\_sp 
 
         As Print ''Hello World!''')
 
     RaisError('Procedure dbo.dba\_indexLookup\_sp created.', 10, 1);
 
 End;
 
 Go
 
  
 
 Set ANSI\_Nulls On;
 
 Set Ansi\_Padding On;
 
 Set Ansi\_Warnings On;
 
 Set ArithAbort On;
 
 Set Concat\_Null\_Yields\_Null On;
 
 SetNoCountOn;
 
 Set Numeric\_RoundAbort Off;
 
 Set Quoted\_Identifier On;
 
 Go
 
  
 
 AlterProcedure dbo.dba\_indexLookup\_sp
 
  
 
         /\* Declare Parameters \*/
 
         @tableName  varchar(128)  =  Null
 
 As
 
 /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
 
     Name:       dba\_indexLookup\_sp
 
  
 
     Author:     Michelle F. Ufford
 
  
 
     Purpose:    Retrieves index information for the specified table.
 
  
 
     Notes:      If the tableName is left null, it will return index 
 
                 information for all tables and indexes.
 
  
 
     Called by:  DBA
 
  
 
     Date        User   Description
 
     \--------------------------------------------------------------------
 
     2008-10-28  MFU    Initial Release
 
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
 
     Exec dbo.dba\_indexLookup\_sp
 
         @tableName = 'myTableName';
 
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/
 
  
 
 SetNoCountOn;
 
 Set XACT\_Abort On;
 
  
 
 Begin
 
  
 
     Declare @objectID int;
 
  
 
     If @tableName Is Not Null
 
         Set @objectID =Object\_ID(@tableName);
 
  
 
     With indexCTE(partition\_scheme\_name
 
                 , partition\_function\_name
 
                 , data\_space\_id)
 
     As(
 
         Select sps.name
 
             , spf.name
 
             , sps.data\_space\_id
 
         From sys.partition\_schemesAs sps
 
         Join sys.partition\_functionsAs spf
 
             On sps.function\_id= spf.function\_id
 
     )
 
  
 
     Select st.nameAs'table\_name'
 
         , IsNull(ix.name, '')As'index\_name'
 
         , ix.object\_id
 
         , ix.index\_id
 
             , Cast(
 
             CaseWhen ix.index\_id=1
 
                     Then'clustered'
 
                 When ix.index\_id=0
 
                     Then'heap'
 
                 Else'nonclustered'End
 
                   \+CaseWhen ix.ignore\_dup\_key<>0
 
                 Then', ignore duplicate keys'
 
                     Else''End
 
                   \+CaseWhen ix.is\_unique<>0
 
                 Then', unique'
 
                     Else''End
 
                   \+CaseWhen ix.is\_primary\_key<>0
 
                 Then', primary key'Else''EndAsvarchar(210)
 
             )As'index\_description'
 
         , IsNull(Replace(Replace(Replace(
 
             (   
 
                 Select c.nameAs'columnName'
 
                 From sys.index\_columnsAs sic
 
                 Join sys.columnsAs c 
 
                     On c.column\_id= sic.column\_id
 
                     And c.object\_id= sic.object\_id
 
                 Where sic.object\_id= ix.object\_id
 
                     And sic.index\_id= ix.index\_id
 
                     And is\_included\_column =0
 
                 OrderBy sic.index\_column\_id
 
                 For XML Raw)
 
                 , '"/><row columnName="', ', ')
 
                 , '<row columnName="', '')
 
                 , '"/>', ''), '')
 
             As'indexed\_columns'
 
         , IsNull(Replace(Replace(Replace(
 
             (   
 
                 Select c.nameAs'columnName'
 
                 From sys.index\_columnsAs sic
 
                 Join sys.columnsAs c 
 
                     On c.column\_id= sic.column\_id
 
                     And c.object\_id= sic.object\_id
 
                 Where sic.object\_id= ix.object\_id
 
                     And sic.index\_id= ix.index\_id
 
                     And is\_included\_column =1
 
                 OrderBy sic.index\_column\_id
 
                 For XML Raw)
 
                 , '"/><row columnName="', ', ')
 
                 , '<row columnName="', '')
 
                 , '"/>', ''), '')
 
             As'included\_columns'
 
         , IsNull(cte.partition\_scheme\_name, '')
 
             As'partition\_scheme\_name'
 
         , Count(partition\_number)As'partition\_count'
 
         , Sum(rows)As'row\_count'
 
     From sys.indexesAs ix
 
     Join sys.partitionsAs sp
 
         On ix.object\_id= sp.object\_id
 
         And ix.index\_id= sp.index\_id
 
     Join sys.tablesAs st
 
         On ix.object\_id= st.object\_id
 
     Left Join indexCTE As cte
 
         On ix.data\_space\_id= cte.data\_space\_id
 
     Where ix.object\_id= IsNull(@objectID, ix.object\_id)
 
     GroupBy st.name
 
         , IsNull(ix.name, '')
 
         , ix.object\_id
 
         , ix.index\_id
 
             , Cast(
 
             CaseWhen ix.index\_id=1
 
                     Then'clustered'
 
                 When ix.index\_id=0
 
                     Then'heap'
 
                 Else'nonclustered'End
 
                   \+CaseWhen ix.ignore\_dup\_key<>0
 
                 Then', ignore duplicate keys'
 
                     Else''End
 
                   \+CaseWhen ix.is\_unique<>0
 
                 Then', unique'
 
                     Else''End
 
                   \+CaseWhen ix.is\_primary\_key<>0
 
                 Then', primary key'Else''EndAsvarchar(210)
 
             )
 
         , IsNull(cte.partition\_scheme\_name, '')
 
         , IsNull(cte.partition\_function\_name, '')
 
     OrderBy table\_name
 
         , index\_id;
 
  
 
     SetNoCountOff;
 
     Return0;
 
 End
 
 Go

[http_www.sqlskills.com_BLOGS_KIMBERLY_post_Updates-_28fixes_29-to-sp_helpindex2.aspx]: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Updates-%28fixes%29-to-sp_helpindex2.aspx
[sp_helpindex2_2005.zip]: http://www.sqlskills.com/blogs/kimberly/content/binary/sp_helpindex2_20051.zip
[sp_helpindex2_2008.zip]: http://www.sqlskills.com/blogs/kimberly/content/binary/sp_helpindex2_20081.zip
[dba_indexLookup_sp]: http://sqlfool.com/2008/10/index-interrogation-script/