[mike_bike_kite]

I'm using the following code to get info about all the indexes within a database, the code works fine (probably cause it's not mine!) but I can't get it to show which indexes are clustered or unique. I did look through the sybase help pages and they indicated certain bits in the status2 field but the values returned didn't match the definitions of the indexes.

Can someone help?

	select	object_name(si.id) as tab,
		si.name + "(" + convert(varchar(3),si.indid) + ")" as idx,
		index_col(object_name(si.id),si.indid,v.number) as col,
		v.number
	into	#indexes
	from	sysindexes si , syscolumns sc , master..spt_values v
	where	si.id = sc.id
		and si.id > 100
		and v.type = "P"
		and v.number >= 1
		and v.number = sc.colid
		and index_col(object_name(si.id),si.indid,v.number) is not null

Mike

[parangiri]

Hi,
You can use the below query to get the required information
columns that can be retrieved are
1) table name
2) column name that is indexed
3) type of index
4) index name


select 'Table name' = object_name(id),'column_name' = index_col(object_name(id),indid,1),
'index_description' = convert(varchar(210), case when (status & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (status & 1)<>0 then ', '+'ignore duplicate keys' else '' end
+ case when (status & 2)<>0 then ', '+'unique' else '' end
+ case when (status & 4)<>0 then ', '+'ignore duplicate rows' else '' end
+ case when (status & 64)<>0 then ', '+'statistics' else case when (status & 32)<>0 then ', '+'hypothetical' else '' end end
+ case when (status & 2048)<>0 then ', '+'primary key' else '' end
+ case when (status & 4096)<>0 then ', '+'unique key' else '' end
+ case when (status & 8388608)<>0 then ', '+'auto create' else '' end
+ case when (status & 16777216)<>0 then ', '+'stats no recompute' else '' end),
'index_name' = name
from sysindexes where (status & 64) = 0
order by id

[mike_bike_kite]

That's exactly what I was after - many thanks!

Mike