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?
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'
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:
