Determining if an index is unique from sysindexes
-
I have a need to use the system tables to create a sql script for the indexes defined for a table. I cannot figure out how to tell whether the index id defined as unique or not from the system tables.
TIA for any help here.
-
I'm not sure about sysindexes, possibly the status column, but you'd have to decipher it.
You could load the output from sp_helpindex into a temporary table, and check the description column to be LIKE '%unique%' .
Steve
-
That is actually found in the sysobjects table like so.
select object_name(o.parent_obj) [Parent Name], o.[name] [Index_Name], o.xtype Type, o.* from sysobjects o
where o.xtype in ('PK','UQ')
-
One might consider having a look at
INDEXPROPERTY(...,'IsUnique')
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You can find the info you need in the master.dbo.spt_values table. Following script lists all indexes that will be uniqe ( unique, primary key or unique key ). I got this info from the sp_helpindex procedure ...
select table_name = object_name(I.id),I.indid, index_name = I.name, I.status
from sysindexes I
where I.indid > 0 and I.indid < 255 and (I.status & 64)=0
and exists ( select 1 from master.dbo.spt_values S where S.type = 'I' and S.name in ( 'unique','primary key','unique key' ) and S.number & I.status <> 0 )
order by id,indid
-
Thanks to all for the ideas. Bert De Haes' answer worked for me.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply