?
Solved

find unique index

Posted on 2009-12-16
5
Medium Priority
?
203 Views
Last Modified: 2012-05-08
what is the syntax to get the unique indexes in the database?

thanks
0
Comment
Question by:anushahanna
5 Comments
 
LVL 7

Assisted Solution

by:VeeNashi
VeeNashi earned 200 total points
ID: 26065254
create unique clustered index <indexname> on <tablename>(columnname)
create unique index <indexname> on <tablename>(columnname)
0
 
LVL 11

Assisted Solution

by:wrmichael
wrmichael earned 500 total points
ID: 26065260
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26065268
thanks VeeNashi, actually i meant to see the existing unique indexes through query.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26065287
wrmichael
the article suggests that the results could be got from SQL Server 2005 catalog views also. wouldn't be much simpler?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1300 total points
ID: 26065356
   SELECT  TABLE_NAME = OBJECT_NAME(i.id), INDEX_NAME = i.name,
            IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
            IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique')
    FROM    sysindexes i
    WHERE   ( i.indid BETWEEN 1 AND 254 )
            AND ( i.Status & 64 ) = 0
            AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0
            AND INDEXPROPERTY(i.id, i.name, 'IsUnique') = 1
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question