/* 
 * Description
 * ===========
 * Stored procedure 'sp_isinix':
 *      - lists indexes containing a column with a specific name
 *
 *
 * Author
 * ======
 * This software was written by Mark Kusma. Please send any 
 * comments, bugs, suggestions etc. to Mark.Kusma@sybase.com.
 *
 *
 * Installation
 * ============
 * Execute this script using "isql", using a login having "sa_role".
 * The stored procedure(s) will be created in the sybsystemprocs database.
 *
 *
 * Revision History
 * ================
 * Version 1.0  17-May-2003  First version
 * 
 *
 * Location
 * ========
 * This file can be found at http://www.sypron.nl/misctools.html .
 *
 *
 * Disclaimer
 * ==========
 * This software is provided "as is"; there is no warranty of any kind.
 * While this software is believed to work accurately, it may not work 
 * correctly and/or reliably in a production environment. In no event shall  
 * Rob Verschoor and/or Sypron B.V. be liable for any damages resulting 
 * from the use of this software. 
 *
 *      
 *---------------------------------------------------------------------------- 
 */

set nocount on
go
set flushmessage on
go

use sybsystemprocs
go

print ' '
print "Creating 'sp_isinix'..."
print "   Usage: sp_isinix <column_name>"
go

if object_id("sp_isinix") <> NULL
  drop proc sp_isinix
go

create proc sp_isinix 
  @col varchar(30) as

declare @tblid int             -- from syscolumns.id
declare @rc    int             -- copy of @@rowcount

   set nocount on

   print "Indexes containing column '%1!' :", @col
   print ' '

   create table #ix (id int, uid int, iname varchar(32))

   set rowcount 31
   select n=identity(2)
   into #n
   from syscolumns
   set rowcount 0

   declare tables cursor for select id from syscolumns where name = @col
   for read only

   open tables

   fetch tables into @tblid 
   while @@sqlstatus = 0
   begin

   insert #ix
   select distinct @tblid, so.uid, si.name
   from sysindexes si, sysobjects so, #n
   where si.id = @tblid
     and so.id = si.id
     and si.indid <= 249
     and index_col(object_name(si.id), si.indid, #n.n, so.uid) = @col

     fetch tables into @tblid  --, @colid
   end

   select substring(user_name(uid) + '.' + object_name(id) + '.' + iname, 1, 78) "owner.table.index"
   from #ix
   order by object_name(id), user_name(uid)
   select @rc = @@rowcount

   print ""
   print "%1! indexes found", @rc
go

print ' '
print 'All done.'
go