Identify index having constraint

• Ak-763166

  Right there with Babe

  Points: 772

  More actions

  January 13, 2009 at 4:46 am

  #92921

  Hi All,

  I have about 500 tables in a database. I have to identify those indexes which have indexes as constraints eg. primary key constraint.

  Is there any sql statement which can help me to retrieve such indexes.

  For example:

  Create table temp(id varchar(10) not null, id2 varchar(10) not null)

  alter table temp add constraint pk_id primary key nonclustered(id)

  create index ix_temp on temp(id2)

  What i want to display is the first inde ie pk_id?

  If you have any idea, please post.

  Cheers

  Amit

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  January 13, 2009 at 4:57 am

  #925758

  Here it is....

  SELECT * FROM sys.indexes WHERE is_primary_key = 1 OR is_unique_constraint = 1

  --Ramesh
  

  ---

• Ak-763166

  Right there with Babe

  Points: 772

  More actions

  January 13, 2009 at 5:02 am

  #925759

  Hi Ramesh,

  Thanks for the reply.

  Does this apply for MS 2000.

  Thanks

  A.

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  January 13, 2009 at 6:33 am

  #925806

  amit (1/13/2009)

  ---

  Hi Ramesh,

  Thanks for the reply.

  Does this apply for MS 2000.

  Thanks

  A.

  Aaaah!! I forgot that I am in 2000 forum..;)

  As per Books Online...

  SQL enforces uniqueness of the column, which is designated as PRIMARY or UNIQUE KEY, by creating a unique index (either clustered or non-clustered) on that column. So, this means that every primary key or unique key will have an index associated with it and also it forces the name of the constraint and the index to be same.

  But I am not entirely sure on Books Online:unsure:....

  So, if BOL is correct then you basically need to query system objects for primary/unique keys....

  SELECT * FROM sysobjects WHERE xtype IN( 'PK', 'UQ' )

  [/CODE]

  --Ramesh
  

  ---