Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Unanswered: Urgent : How to find whih table has unique index

    I have many databses and we are trying to see how many can be replicated. Found out 90 on't have primary keys.
    Next option, find unique indexed tables and convert them to PKeys. Now my question is how do I fin that a table has a unique index and column has "allow null" Please remember I am not asking to find Unique constraint.

    In other words , folowing query
    select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE in('UNIQUE') won't tell you if a table has a unique index.

    Please help.

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    Check out sqlservercentral.com's script section. They have a script that will return every unique index on each table in a database plus a lot of other good stuff.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    BOL:

    SQL Server automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Therefore, if an attempt to insert a duplicate row is made, SQL Server returns an error message that says the UNIQUE constraint has been violated and does not add the row to the table. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.

    drop table test
    create table test(id int unique)
    go
    select sy.*
    from sysindexes sy
    join sysobjects so on so.id=sy.id and so.name='test'
    where sy.indid not in(0,255)

    select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE in('UNIQUE')

  4. #4
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Ok

    Let me rephrase this. Is there an SQL which gives me name of table which have either a UNIQUE INDEX or UNIQUE CONSTRAINT. Problem is if you look in Table_constraints VIEW , it won't list table name which has a UNIQUE INDEX and will only list a table which has UNIQUE_CONSTRAINT. Hope that explains what I am looking for. Thanks

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    SELECT so.name
       FROM dbo.sysobjects AS so
       WHERE  EXISTS (SELECT *
          FROM dbo.sysindexes AS si
          WHERE  si.id = so.id
             AND 1 = IndexProperty(si.id, si.name, 'IsUnique'))
       ORDER BY so.name
    -PatP

  6. #6
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Great

    It works ! thanks a lot

Posting Permissions

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