Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527

    Unanswered: sysindexes question - testing for unique and/or clustered indexes

    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?
    Code:
    	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

  2. #2
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    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

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    That's exactly what I was after - many thanks!

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •