use master
GO

if exists (select * from sysobjects where id = object_id('dbo.sp_ABIndexInfo') and sysstat & 0xf = 4)
        drop procedure dbo.sp_ABIndexInfo
GO

create procedure sp_ABIndexInfo (@TableName varchar(30)='')
/* 
   Name:  sp_ABIndexInfo 
   
   Purpose: show information about indexes.  

   Author:  Vince Iacoboni
            vince.iacoboni@btalexbrown.com

   7/16/97  VRI  Created.
*/

as

    select @TableName = rtrim(@TableName) + '%'

    select o.name TableName,
           i.name IndexName, 
           case indid when 0 then 'No index' when 1 then 'Cluster' else 'Non-cluster' end ClusterType,
           case when (status & (2|4096)) > 0 then 'Unique' else ' ' end UniqueType,
           case when (status & 2048) > 0 then 'Primary Key' else ' ' end PK,
           index_col(o.name, indid, 1) ColName1,
           index_col(o.name, indid, 2) ColName2,
           index_col(o.name, indid, 3) ColName3,
           index_col(o.name, indid, 4) ColName4,
           index_col(o.name, indid, 5) ColName5,
           index_col(o.name, indid, 6) ColName6,
           index_col(o.name, indid, 7) ColName7,
           index_col(o.name, indid, 8) ColName8
      from sysindexes i, 
           sysobjects o
      where i.id = o.id
        and o.type = 'U'
        and indid < 255
        and o.name like @TableName
      order by 1, indid

GO

grant execute on sp_ABIndexInfo to public
GO