1

What is the most efficient way in SQL Server to identify all tables that have a unique index?

This has been addressed elsewhere for Oracle: https://stackoverflow.com/a/28740458/3112914

My end goal is to identify tables in a database that can not be compared using SSDT Data Compare tool. For that tool to work "Tables must have the same primary key, unique index, or unique constraint." I can identify those with primary key or unique constraint using OBJECTPROPERTY, e.g.

SELECT
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS TableName,
    OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') AS HasPrimaryKey,
    OBJECTPROPERTY(OBJECT_ID,'TableHasUniqueCnst') AS HasUniqueConstraint
FROM
    sys.tables

There is an IsIndexed property but that doesn't say that is a unique index. https://learn.microsoft.com/en-us/sql/t-sql/functions/objectproperty-transact-sql?view=sql-server-2017

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
weblorin
  • 328
  • 3
  • 11
  • pretty much a dupe of: https://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db Just run that guys query and flip `AND ind.is_unique = 1` – sniperd Jul 11 '18 at 19:34
  • If by "pretty much a dupe of" @sniperd means, this "related and should help you", okay. In case somebody just wants to jump to the answer to this exact question, I've put it together and posted it below. – weblorin Jul 11 '18 at 23:48
  • 1
    Welcome to StackOverflow! Dupes are pointed out to help the person asking the question and to act as sign posts for others to find the same question due to different wording of the questions. I didn't mean your question was invalid and in fact is a very good one and is why I didn't vote to close it. I sorry if you feel offended. Nice job writing up a well written answer :) – sniperd Jul 12 '18 at 00:18

1 Answers1

1

If you want to list all the tables that have a unique index you can join to sys.indexes and filter using column is_unique.

e.g. 

-- list of tables that have a unique index
SELECT SCHEMA_NAME(schema_id) AS SchemaName,
    name AS TableName
FROM 
    sys.tables
WHERE EXISTS (SELECT *
    FROM sys.indexes i
    WHERE i.object_id = tables.object_id AND is_unique = 1)

If you want to extend the show every table, along with a value indicating whether or not it has a unique index (ie extend the start you had above), you can wrap that EXISTS statement in an IIF or CASE, like this : 

SELECT SCHEMA_NAME(schema_id) AS SchemaName,
    name AS TableName,
    OBJECTPROPERTY(OBJECT_ID, 'TableHasPrimaryKey') AS HasPrimaryKey,
    OBJECTPROPERTY(OBJECT_ID, 'TableHasUniqueCnst') AS HasUniqueConstraint,
    CASE 
        WHEN EXISTS (
                SELECT *
                FROM sys.indexes i
                WHERE i.object_id = tables.object_id
                    AND is_unique = 1
                )
            THEN 1
        ELSE 0
        END AS HasUniqueIndex
FROM sys.tables
weblorin
  • 328
  • 3
  • 11