Obtains the index information of all tables in the database.

Create proc sp_nchelpindex
@ Objname varchar (256), -- table name
@ Clust varchar (20) = 'all', -- clustered only clustered index, nonclustered only non clustered Index
@ PK varchar (10) = 'all' -- pkonly primary key index, nopk non-primary key index

/*
Obtains the index information of all tables in the database.
-- Create in the master database and then use it directly in any database

Usage:
Sp_nchelpindex @ objname = table name
@ Clust = clustered only clustered indexes, nonclustered only non-clustered Indexes
@ PK = pkonly primary key index, nopk non-primary key index

Advanced usage:

Create a temporary table to store data:
Create Table # indexinfo
(Tablename varchar (256 ),
Indexname varchar (256 ),
Index_keys varchar (256 ),
Isclustered varchar (3 ),
Isprimary_key varchar (3 ),
Idxfilegroup varchar (100 ),
Index_description varchar (200 ))

Execute scripts for each table in the database using sp_msforeachtable

-- Insert all indexes
Sp_msforeachtable "insert into # Table exec sp_nchelpindex '? '"
-- Insert clustered index information
Sp_msforeachtable "insert into # Table exec sp_nchelpindex '? ', 'Clustered '"
-- Insert primary key index information
Sp_msforeachtable "insert into # Table exec sp_nchelpindex '? ', '', 'Pkonly '"

Query data: Select * from # indexinfo

Published in http://www.sqlservercentral.com/scripts/contributions/1082.asp
Vivianfdlpw 2005.10.24 reference please keep this information
*/
As

Set nocount on

Declare @ objid int, -- the Object ID of the table
@ Indid smallint, -- the index ID of an index
@ Groupid smallint, -- the filegroup ID of an index
@ Indname sysname,
@ Groupname sysname,
@ Status int,
@ Keys nvarchar (2126), -- length (16 * max_identifierlength) + (15*2) + (16*3)
@ Dbname sysname

-- Check to see that the object names are local to the current database.
Select @ dbname = parsename (@ objname, 3)

If @ dbname is not null and @ dbname <> db_name ()
Begin
Raiserror (15250,-1,-1)
Return (1)
End

-- Check to see the table exists and initialize @ objid.
Select @ objid = object_id (@ objname)
If @ objid is null
Begin
Select @ dbname = db_name ()
Raiserror (15009,-1,-1, @ objname, @ dbname)
Return (1)
End

-- Open cursor over indexes (skip stats: Bug shiloh_000096)
Declare ms_crs_ind cursor local static
Select indid, groupid, name, status from sysindexes
Where id = @ objid and indid> 0 and indid <255 and (Status & 64) = 0 order by indid
Open ms_crs_ind
Fetch ms_crs_ind into @ indid, @ groupid, @ indname, @ status

-- If no index, quit
If @ fetch_status <0
Begin
Deallocate ms_crs_ind
Raiserror (15472,-1,-1) -- 'object does not have any indexes .'
Return (0)
End

-- Create temp table
Create Table # spindtab
(
Index_name sysname collate database_default not null,
Stats int,
Groupname sysname collate database_default not null,
Index_keys nvarchar (2126) Collate database_default not null -- see @ keys above for length descr
)

-- Now check out each index, figure out its type and keys and
-- Save the info in a temporary table that we'll print out at the end.
While @ fetch_status> = 0
Begin
-- First we'll figure out what the keys are.
Declare @ I int, @ thiskey nvarchar (131) -- 128 + 3

Select @ keys = index_col (@ objname, @ indid, 1), @ I = 2
If (indexkey_property (@ objid, @ indid, 1, 'isdesending') = 1)
Select @ keys = @ keys + '(-)'

Select @ thiskey = index_col (@ objname, @ indid, @ I)
If (@ thiskey is not null) and (indexkey_property (@ objid, @ indid, @ I, 'isdesending') = 1 ))
Select @ thiskey = @ thiskey + '(-)'

While (@ thiskey is not null)
Begin
Select @ keys = @ keys + ',' + @ thiskey, @ I = @ I + 1
Select @ thiskey = index_col (@ objname, @ indid, @ I)
If (@ thiskey is not null) and (indexkey_property (@ objid, @ indid, @ I, 'isdesending') = 1 ))
Select @ thiskey = @ thiskey + '(-)'
End

Select @ groupname = groupname from sysfilegroups where groupid = @ groupid

-- Insert row for Index
Insert into # spindtab values (@ indname, @ status, @ groupname, @ keys)

-- Next Index
Fetch ms_crs_ind into @ indid, @ groupid, @ indname, @ status
End
Deallocate ms_crs_ind

-- Set up some constant values for output Query
Declare @ empty varchar (1) Select @ empty =''
Declare @ DES1 varchar (35), -- 35 matches spt_values
@ Des2 varchar (35 ),
@ Des4 varchar (35 ),
@ Des32 varchar (35 ),
@ Des64 varchar (35 ),
@ Des2048 varchar (35 ),
@ Des4096 varchar (35 ),
@ Des8388608 varchar (35 ),
@ Des16777216 varchar (35)
Select @ DES1 = Name from Master. DBO. spt_values where type = 'I' and number = 1
Select @ des2 = Name from Master. DBO. spt_values where type = 'I' and number = 2
Select @ des4 = Name from Master. DBO. spt_values where type = 'I' and number = 4
Select @ des32 = Name from Master. DBO. spt_values where type = 'I' and number = 32
Select @ des64 = Name from Master. DBO. spt_values where type = 'I' and number = 64
Select @ des2048 = Name from Master. DBO. spt_values where type = 'I' and number = 2048
Select @ des4096 = Name from Master. DBO. spt_values where type = 'I' and number = 4096
Select @ des8388608 = Name from Master. DBO. spt_values where type = 'I' and number = 8388608
Select @ des16777216 = Name from Master. DBO. spt_values where type = 'I' and number = 16777216

-- Display the results
If @ PK = 'pkonly'
Begin

If @ clust = 'clustered'
Begin
Select
'Tablename' = @ objname,
'Index _ name' = index_name,
'Index _ keys '= index_keys,
'Isclustered' = case when (Stats & 16) <> 0 then 'yes' else 'no' end,
'Isprimary _ key' = case when (Stats & 2048) <> 0 then 'yes' else 'no' end,
'Idxfilegroup' = groupname,
'Index _ description' = convert (varchar (210), -- bits 16 off, 1, 2, 16777216 on, located on Group
Case when (Stats & 16) <> 0 then 'clustered' else' nonclustered' end
+ Case when (Stats & 1) <> 0 then ',' + @ DES1 else @ empty end
+ Case when (Stats & 2) <> 0 then ',' + @ des2 else @ empty end
+ Case when (Stats & 4) <> 0 then ',' + @ des4 else @ empty end
+ Case when (Stats & 64) <> 0 then ',' + @ des64 else case when (Stats & 32) <> 0 then ', '+ @ des32 else @ empty end
+ Case when (Stats & 2048) <> 0 then ',' + @ des2048 else @ empty end
+ Case when (Stats & 4096) <> 0 then ',' + @ des4096 else @ empty end
+ Case when (Stats & 8388608) <> 0 then ',' + @ des8388608 else @ empty end
+ Case when (Stats & 16777216) <> 0 then ',' + @ des16777216 else @ empty end
+ 'Located on' + groupname)
From # spindtab
Where (Stats> 16) <> 0 and (Stats & 2048) <> 0
Order by index_name
 
End
Else
If @ clust = 'nonclustered'
Begin
Select
'Tablename' = @ objname,
'Index _ name' = index_name,
'Index _ keys '= index_keys,
'Isclustered' = case when (Stats & 16) <> 0 then 'yes' else 'no' end,
'Isprimary _ key' = case when (Stats & 2048) <> 0 then 'yes' else 'no' end,
'Idxfilegroup' = groupname,
'Index _ description' = convert (varchar (210), -- bits 16 off, 1, 2, 16777216 on, located on Group
Case when (Stats & 16) <> 0 then 'clustered' else' nonclustered' end
+ Case when (Stats & 1) <> 0 then ',' + @ DES1 else @ empty end
+ Case when (Stats & 2) <> 0 then ',' + @ des2 else @ empty end
+ Case when (Stats & 4) <> 0 then ',' + @ des4 else @ empty end
+ Case when (Stats & 64) <> 0 then ',' + @ des64 else case when (Stats & 32) <> 0 then ', '+ @ des32 else @ empty end
+ Case when (Stats & 2048) <> 0 then ',' + @ des2048 else @ empty end
+ Case when (Stats & 4096) <> 0 then ',' + @ des4096 else @ empty end
+ Case when (Stats & 8388608) <> 0 then ',' + @ des8388608 else @ empty end
+ Case when (Stats & 16777216) <> 0 then ',' + @ des16777216 else @ empty end
+ 'Located on' + groupname)
From # spindtab
Where (Stats & 16) = 0 and (Stats & 2048) <> 0
Order by index_name
 
End
Else

Begin
Select
'Tablename' = @ objname,
'Index _ name' = index_name,
'Index _ keys '= index_keys,
'Isclustered' = case when (Stats & 16) <> 0 then 'yes' else 'no' end,
'Isprimary _ key' = case when (Stats & 2048) <> 0 then 'yes' else 'no' end,
'Idxfilegroup' = groupname,
'Index _ description' = convert (varchar (210), -- bits 16 off, 1, 2, 16777216 on, located on Group
Case when (Stats & 16) <> 0 then 'clustered' else' nonclustered' end
+ Case when (Stats & 1) <> 0 then ',' + @ DES1 else @ empty end
+ Case when (Stats & 2) <> 0 then ',' + @ des2 else @ empty end
+ Case when (Stats & 4) <> 0 then ',' + @ des4 else @ empty end
+ Case when (Stats & 64) <> 0 then ',' + @ des64 else case when (Stats & 32) <> 0 then ', '+ @ des32 else @ empty end
+ Case when (Stats & 2048) <> 0 then ',' + @ des2048 else @ empty end
+ Case when (Stats & 4096) <> 0 then ',' + @ des4096 else @ empty end
+ Case when (Stats & 8388608) <> 0 then ',' + @ des8388608 else @ empty end
+ Case when (Stats & 16777216) <> 0 then ',' + @ des16777216 else @ empty end
+ 'Located on' + groupname)

From # spindtab where (Stats & 2048) <> 0
Order by index_name
 
End
End
Else

If @ PK = 'nopk'
Begin

If @ clust = 'clustered'
Begin
Select
'Tablename' = @ objname,
'Index _ name' = index_name,
'Index _ keys '= index_keys,
'Isclustered' = case when (Stats & 16) <> 0 then 'yes' else 'no' end,
'Isprimary _ key' = case when (Stats & 2048) <> 0 then 'yes' else 'no' end,
'Idxfilegroup' = groupname,
'Index _ description' = convert (varchar (210), -- bits 16 off, 1, 2, 16777216 on, located on Group
Case when (Stats & 16) <> 0 then 'clustered' else' nonclustered' end
+ Case when (Stats & 1) <> 0 then ',' + @ DES1 else @ empty end
+ Case when (Stats & 2) <> 0 then ',' + @ des2 else @ empty end
+ Case when (Stats & 4) <> 0 then ',' + @ des4 else @ empty end
+ Case when (Stats & 64) <> 0 then ',' + @ des64 else case when (Stats & 32) <> 0 then ', '+ @ des32 else @ empty end
+ Case when (Stats & 2048) <> 0 then ',' + @ des2048 else @ empty end
+ Case when (Stats & 4096) <> 0 then ',' + @ des4096 else @ empty end
+ Case when (Stats & 8388608) <> 0 then ',' + @ des8388608 else @ empty end
+ Case when (Stats & 16777216) <> 0 then ',' + @ des16777216 else @ empty end
+ 'Located on' + groupname)
From # spindtab
Where (Stats & 16) <> 0 and (Stats & 2048) = 0
Order by index_name
 
End
Else
If @ clust = 'nonclustered'
Begin
Select
'Tablename' = @ objname,
'Index _ name' = index_name,
'Index _ keys '= index_keys,
'Isclustered' = case when (Stats & 16) <> 0 then 'yes' else 'no' end,
'Isprimary _ key' = case when (Stats & 2048) <> 0 then 'yes' else 'no' end,
'Idxfilegroup' = groupname,
'Index _ description' = convert (varchar (210), -- bits 16 off, 1, 2, 16777216 on, located on Group
Case when (Stats & 16) <> 0 then 'clustered' else' nonclustered' end
+ Case when (Stats & 1) <> 0 then ',' + @ DES1 else @ empty end
+ Case when (Stats & 2) <> 0 then ',' + @ des2 else @ empty end
+ Case when (Stats & 4) <> 0 then ',' + @ des4 else @ empty end
+ Case when (Stats & 64) <> 0 then ',' + @ des64 else case when (Stats & 32) <> 0 then ', '+ @ des32 else @ empty end
+ Case when (Stats & 2048) <> 0 then ',' + @ des2048 else @ empty end
+ Case when (Stats & 4096) <> 0 then ',' + @ des4096 else @ empty end
+ Case when (Stats & 8388608) <> 0 then ',' + @ des8388608 else @ empty end
+ Case when (Stats & 16777216) <> 0 then ',' + @ des16777216 else @ empty end
+ 'Located on' + groupname)
From # spindtab
Where (Stats & 16) = 0 and (Stats & 2048) = 0
Order by index_name
 
End
Else

Begin
Select
'Tablename' = @ objname,
'Index _ name' = index_name,
'Index _ keys '= index_keys,
'Isclustered' = case when (Stats & 16) <> 0 then 'yes' else 'no' end,
'Isprimary _ key' = case when (Stats & 2048) <> 0 then 'yes' else 'no' end,
'Idxfilegroup' = groupname,
'Index _ description' = convert (varchar (210), -- bits 16 off, 1, 2, 16777216 on, located on Group
Case when (Stats & 16) <> 0 then 'clustered' else' nonclustered' end
+ Case when (Stats & 1) <> 0 then ',' + @ DES1 else @ empty end
+ Case when (Stats & 2) <> 0 then ',' + @ des2 else @ empty end
+ Case when (Stats & 4) <> 0 then ',' + @ des4 else @ empty end
+ Case when (Stats & 64) <> 0 then ',' + @ des64 else case when (Stats & 32) <> 0 then ', '+ @ des32 else @ empty end
+ Case when (Stats & 2048) <> 0 then ',' + @ des2048 else @ empty end
+ Case when (Stats & 4096) <> 0 then ',' + @ des4096 else @ empty end
+ Case when (Stats & 8388608) <> 0 then ',' + @ des8388608 else @ empty end
+ Case when (Stats & 16777216) <> 0 then ',' + @ des16777216 else @ empty end
+ 'Located on' + groupname)

From # spindtab where (Stats & 2048) = 0
Order by index_name
 
End
End
Else

 
Begin

If @ clust = 'clustered'
Begin
Select
'Tablename' = @ objname,
'Index _ name' = index_name,
'Index _ keys '= index_keys,
'Isclustered' = case when (Stats & 16) <> 0 then 'yes' else 'no' end,
'Isprimary _ key' = case when (Stats & 2048) <> 0 then 'yes' else 'no' end,
'Idxfilegroup' = groupname,
'Index _ description' = convert (varchar (210), -- bits 16 off, 1, 2, 16777216 on, located on Group
Case when (Stats & 16) <> 0 then 'clustered' else' nonclustered' end
+ Case when (Stats & 1) <> 0 then ',' + @ DES1 else @ empty end
+ Case when (Stats & 2) <> 0 then ',' + @ des2 else @ empty end
+ Case when (Stats & 4) <> 0 then ',' + @ des4 else @ empty end
+ Case when (Stats & 64) <> 0 then ',' + @ des64 else case when (Stats & 32) <> 0 then ', '+ @ des32 else @ empty end
+ Case when (Stats & 2048) <> 0 then ',' + @ des2048 else @ empty end
+ Case when (Stats & 4096) <> 0 then ',' + @ des4096 else @ empty end
+ Case when (Stats & 8388608) <> 0 then ',' + @ des8388608 else @ empty end
+ Case when (Stats & 16777216) <> 0 then ',' + @ des16777216 else @ empty end
+ 'Located on' + groupname)
From # spindtab
Where (Stats & 16) <> 0
Order by index_name
 
End
Else
If @ clust = 'nonclustered'
Begin
Select
'Tablename' = @ objname,
'Index _ name' = index_name,
'Index _ keys '= index_keys,
'Isclustered' = case when (Stats & 16) <> 0 then 'yes' else 'no' end,
'Isprimary _ key' = case when (Stats & 2048) <> 0 then 'yes' else 'no' end,
'Idxfilegroup' = groupname,
'Index _ description' = convert (varchar (210), -- bits 16 off, 1, 2, 16777216 on, located on Group
Case when (Stats & 16) <> 0 then 'clustered' else' nonclustered' end
+ Case when (Stats & 1) <> 0 then ',' + @ DES1 else @ empty end
+ Case when (Stats & 2) <> 0 then ',' + @ des2 else @ empty end
+ Case when (Stats & 4) <> 0 then ',' + @ des4 else @ empty end
+ Case when (Stats & 64) <> 0 then ',' + @ des64 else case when (Stats & 32) <> 0 then ', '+ @ des32 else @ empty end
+ Case when (Stats & 2048) <> 0 then ',' + @ des2048 else @ empty end
+ Case when (Stats & 4096) <> 0 then ',' + @ des4096 else @ empty end
+ Case when (Stats & 8388608) <> 0 then ',' + @ des8388608 else @ empty end
+ Case when (Stats & 16777216) <> 0 then ',' + @ des16777216 else @ empty end
+ 'Located on' + groupname)
From # spindtab
Where (Stats & 16) = 0
Order by index_name
 
End
Else

Begin
Select
'Tablename' = @ objname,
'Index _ name' = index_name,
'Index _ keys '= index_keys,
'Isclustered' = case when (Stats & 16) <> 0 then 'yes' else 'no' end,
'Isprimary _ key' = case when (Stats & 2048) <> 0 then 'yes' else 'no' end,
'Idxfilegroup' = groupname,
'Index _ description' = convert (varchar (210), -- bits 16 off, 1, 2, 16777216 on, located on Group
Case when (Stats & 16) <> 0 then 'clustered' else' nonclustered' end
+ Case when (Stats & 1) <> 0 then ',' + @ DES1 else @ empty end
+ Case when (Stats & 2) <> 0 then ',' + @ des2 else @ empty end
+ Case when (Stats & 4) <> 0 then ',' + @ des4 else @ empty end
+ Case when (Stats & 64) <> 0 then ',' + @ des64 else case when (Stats & 32) <> 0 then ', '+ @ des32 else @ empty end
+ Case when (Stats & 2048) <> 0 then ',' + @ des2048 else @ empty end
+ Case when (Stats & 4096) <> 0 then ',' + @ des4096 else @ empty end
+ Case when (Stats & 8388608) <> 0 then ',' + @ des8388608 else @ empty end
+ Case when (Stats & 16777216) <> 0 then ',' + @ des16777216 else @ empty end
+ 'Located on' + groupname)

From # spindtab
Order by index_name
 
End
End
Return (0) -- sp_helpindex

Go