SQL Server Performance

How to find Unique key in a table

Discussion in 'General DBA Questions' started by thugs, Oct 12, 2007.

  1. thugs New Member

    Can anybody tell me how to find unique key ina table
    Thanks.
  2. Adriaan New Member

    Do you have to find the unique key for a heap of data, so you can set the primary key for the table?
    Or do you have to find the existing unique constraint on an existing table?
  3. thugs New Member

    i have a table which don't have any primary key or any index on it.
    I want to know wthere there is any unique key on the table.
  4. Adriaan New Member

    The quick-and-dirty way is to add a unique constraint to the table in Enterprise Manager, and just test to see if it is accepted.
    For a more serious approach, you need to run some queries.
    First, get the total number of rows in the table:
    SELECT COUNT(*) FROM MyTable
    Next, determine the number of distinct values per column:
    SELECT COUNT(DISTINCT Column1) FROM MyTable
    SELECT COUNT(DISTINCT Column2) FROM MyTable
    etc.
    If the count for one of the columns is identical to the total number of rows, then you have the unique key.
    But if there is no single column that is a unique key, then you have to run queries for combinations of columns:
    SELECT COUNT(*) FROM (SELECT DISTINCT Column1, Column2 FROM MyTable) X
    SELECT COUNT(*) FROM (SELECT DISTINCT Column1, Column3 FROM MyTable) X
    SELECT COUNT(*) FROM (SELECT DISTINCT Column2, Column3 FROM MyTable) X
    etc.
    You may need to add more columns:
    SELECT COUNT(*) FROM (SELECT DISTINCT Column1, Column2, Column3 FROM MyTable) X
    etc.
    If you cannot find any key this way, there must be duplicate rows in the table.
  5. Madhivanan Moderator

    or if you want to know if a table has any unique constraints, then
    select OBJECTPROPERTY ( object_id('table_name'),'TableHasUniqueCnst')
    If the result is 1 then it has
  6. thugs New Member

    Thanks for the replies
  7. Madhivanan Moderator

    [quote user="thugs"]
    Thanks for the replies
    [/quote]
    I am interested to know which method you are going to use [:)]
  8. thugs New Member

    Madhivanan ,
    i want the result2
    so i am using the script posted by adriaan.

Share This Page