Get the list of unique constraints and indexes in a database

Question

I have to get the list of all the unique key constraints and the indexes of a particular database. I am doing something like this:

SELECT * FROM sys.sysobjects WHERE type!='u' AND name LIKE <tablename> 

Just wanted to confirm if this was the correct way, or is there a better way of doing the same thing?

Answer 1

Since unique constraints are implemented under the covers as indexes, you can get all of this information directly from sys.indexes:

SELECT
  [schema] = OBJECT_SCHEMA_NAME([object_id]),
  [table]  = OBJECT_NAME([object_id]),
  [index]  = name, 
  is_unique_constraint,
  is_unique,
  is_primary_key
FROM sys.indexes
-- WHERE [object_id] = OBJECT_ID('dbo.tablename');

To repeat for all databases (and presumably without the filter for a specific table):

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 'SELECT db = ' + name + ',
    [schema] = OBJECT_SCHEMA_NAME([object_id]),
    [table]  = OBJECT_NAME([object_id]),
    [index]  = name, 
    is_unique_constraint,
    is_unique,
    is_primary_key
  FROM ' + QUOTENAME(name) + '.sys.indexes;'
FROM sys.databases
WHERE database_id BETWEEN 4 AND 32766;

EXEC sp_executesql @sql;

Answer 2

The other answers did not return complete lists for me. This query worked for me to return all unique indexes that are not primary keys or system tables:

select i.name as index_name, o.name as object_name
from sys.indexes i
    join sys.objects o on i.object_id= o.object_id
where (i.is_unique_constraint = 1 OR i.is_unique = 1) 
    and i.is_primary_key = 0 and o.type_desc <> 'SYSTEM_TABLE'

Answer 3

A unique constraint is represented in sys.objects by the type 'UQ'

 select name from sys.objects where type='UQ'

To get the indexes

 select i.name, o.name from sys.indexes i
inner join sys.objects o on i.object_id= o.object_id

Answer 4

You can get the unique key constraints, and indexes from sys.indexes. Specifically, unique constraints:

select * from sys.indexes where is_unique_constraint = 1

Answer 5

I was looking for a solution - find all the unique indexes on a specific table. and I came to this question as a starting point.

at the end I put together my own script and I will share it here.

The good thing about this script is that it gives the columns of the index.

All the glory go to the author of the original script, the link is included in the code.

--====================================================================================
-- Display SQL Server Index Details – Type, Key Columns, Included Columns
-- https://www.ptr.co.uk/blog/sql-server-display-indexes-their-columns-included-columns
--====================================================================================


--Display all indexes along with key columns, included columns and index type

DECLARE @TempTable AS TABLE (SchemaName VARCHAR(100), 
                             ObjectID INT, 
                             TableName VARCHAR(100), 
                             IndexID INT, 
                             IndexName VARCHAR(100), 
                             ColumnID INT, 
                             column_index_id INT, 
                             ColumnNames  VARCHAR(500), 
                             IncludeColumns  VARCHAR(500), 
                             NumberOfColumns INT, 
                             IndexType  VARCHAR(20),
                             IS_UNIQUE BIT,
                             Is_Primary_Key BIT,
                             Is_Unique_Constraint BIT,
                             LastColRecord INT);

WITH CTE_Indexes (SchemaName, ObjectID, TableName, IndexID, IndexName, 
                  ColumnID, column_index_id, ColumnNames, IncludeColumns, 
                  NumberOfColumns, IndexType, Is_Unique,Is_Primary_Key,Is_Unique_Constraint)
AS
(
SELECT s.name, t.object_id, t.name, i.index_id, i.name, c.column_id, ic.index_column_id,
        CASE ic.is_included_column WHEN 0 THEN CAST(c.name AS VARCHAR(5000)) ELSE '' END, 
        CASE ic.is_included_column WHEN 1 THEN CAST(c.name AS VARCHAR(5000)) ELSE '' END, 
        1, i.type_desc,I.is_unique,i.Is_Primary_Key,i.Is_Unique_Constraint 
    FROM  sys.schemas AS s
        JOIN sys.tables AS t ON s.schema_id = t.schema_id
            JOIN sys.indexes AS i ON i.object_id = t.object_id
                JOIN sys.index_columns AS ic 
                  ON ic.index_id = i.index_id 
                 AND ic.object_id = i.object_id
                    JOIN sys.columns AS c 
                      ON c.column_id = ic.column_id 
                     AND c.object_id = ic.object_id
                     AND ic.index_column_id = 1
UNION ALL
SELECT s.name, t.object_id, t.name, i.index_id, i.name, c.column_id, ic.index_column_id,
        CASE ic.is_included_column WHEN 0 THEN CAST(cte.ColumnNames + ', ' + c.name AS VARCHAR(5000))  
                                          ELSE cte.ColumnNames END, 
        CASE  
            WHEN ic.is_included_column = 1 AND cte.IncludeColumns != '' 
                THEN CAST(cte.IncludeColumns + ', ' + c.name AS VARCHAR(5000))
            WHEN ic.is_included_column =1 AND cte.IncludeColumns = '' 
                THEN CAST(c.name AS VARCHAR(5000)) 
            ELSE '' 
        END,
        cte.NumberOfColumns + 1, i.type_desc,I.is_unique,I.Is_Primary_Key,i.Is_Unique_Constraint 
    FROM  sys.schemas AS s
        JOIN sys.tables AS t ON s.schema_id = t.schema_id
            JOIN sys.indexes AS i ON i.object_id = t.object_id
                JOIN sys.index_columns AS ic 
                  ON ic.index_id = i.index_id 
                 AND ic.object_id = i.object_id
                    JOIN sys.columns AS c 
                      ON c.column_id = ic.column_id 
                     AND c.object_id = ic.object_id 
                    JOIN CTE_Indexes cte 
                      ON cte.Column_index_ID + 1 = ic.index_column_id  
                    --JOIN CTE_Indexes cte ON cte.ColumnID + 1 = ic.index_column_id  
                     AND cte.IndexID = i.index_id AND cte.ObjectID = ic.object_id

)
INSERT INTO  @TempTable 
SELECT *, 
  RANK() OVER (PARTITION BY ObjectID, IndexID ORDER BY NumberOfColumns DESC) AS LastRecord 
  FROM CTE_Indexes AS cte;

SELECT SchemaName, TableName, IndexName, ColumnNames, IncludeColumns, IndexType, Is_Unique,Is_Primary_Key,Is_Unique_Constraint   
  FROM @TempTable
 WHERE LastColRecord = 1
 --WHERE T.object_id=OBJECT_ID('dbo.invoiceSelectedOptionalFees')
 --AND objectid=OBJECT_ID('dbo.accountTransaction')
 AND objectid=OBJECT_ID('dbo.invoiceSelectedOptionalFees')
ORDER BY objectid, TableName, indexid, IndexName

in the example above I used the following table:

IF OBJECT_ID('[dbo].[invoiceSelectedOptionalFees]') IS NOT NULL 
DROP TABLE [dbo].[invoiceSelectedOptionalFees] 
GO
CREATE TABLE [dbo].[invoiceSelectedOptionalFees] ( 
[Id]         INT              IDENTITY(1,1)                 NOT NULL,
[invoiceId]  INT                              NOT NULL,
[feeTypeId]  INT                              NOT NULL,
CONSTRAINT   [PK__invoiceS__3214EC0703C8693D]  PRIMARY KEY CLUSTERED    ([Id] asc),
CONSTRAINT   [CK_invoiceSelectedOptionalFees_invoice_feeType]  UNIQUE      NONCLUSTERED ([invoiceId] asc, [feeTypeId] asc),
CONSTRAINT   [FK_invoiceSelectedOptionalFees_invoice]                      FOREIGN KEY ([invoiceId]) REFERENCES [invoice]([invoiceID]),
CONSTRAINT   [FK_invoiceSelectedOptionalFees_feeTypes]                     FOREIGN KEY ([feeTypeId]) REFERENCES [feeTypes]([feeTypeID]))

and that gave me the following result: