Post reply

How to know if an index is unique or not??

  • October 14, 2008 at 2:25 am

    #72141

    IS there any column in the sysindexes table which highlight whethere an index is unique or not?

    If not, is there any sql to know the unique index in each table?

    Thanks for your support.

  • October 14, 2008 at 2:35 am

    #884468

    select object_name(id), name from sysindexes where INDEXPROPERTY ( id , name , 'IsUnique') = 1

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 14, 2008 at 4:13 am

    #884501

    I want to know whether an index found on the linked server is clustered or not. i may be wrong but I believe indexproperty wont work here. Is there any other alternative way.

    Thanks

  • October 14, 2008 at 5:30 am

    #884542

    Index property works just fine there too.

    select object_name(id), name from sysindexes where INDEXPROPERTY ( id , name , 'IsClustered') = 1

    Use that within an openquery and it'll work. Or you can just check the indid. An index id of 1 is a clustered index. 2-249 are nonclustered or statistics.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply