Query to pull all unique indexes from all tables in database using sql

Question

Query to pull all unique indexes from all tables in database using sql. The output should display schema name, table name and column name.

I tried the following query:

SELECT
     t.[name] AS TableName
    ,i.[name] AS IndexName
    ,c.[name] AS ColumnName
FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
    AND s.[name] = 'dbo'
INNER JOIN sys.columns c ON c.object_id = t.object_id
INNER JOIN sys.indexes i ON i.object_id = t.object_id
    AND i.index_id > 0
    AND i.is_primary_key = 0
    AND i.is_unique = 1
INNER JOIN sys.index_columns ic ON ic.object_id = t.object_id
    AND ic.column_id = c.column_id

but it displays TableName and IndexName multiple times if they have more than one unique index column.

It want to display:

TableName   IndexName           ColumnName
Customers   IX_customer_name    FirstName, LastName

Answer 1

You can use the sp_MSforEachDB stored procedure:

EXECUTE master.sys.sp_MSforeachdb '
USE [?];
select db_name(), *
from sys.indexes
where is_unique = 1';

You can also exclude system databases by adding if db_id() > 4 begin end.