List Indexes, Constraints, and Triggers on MS SQL Server (KBA5848)

Query: List all Triggers

This sample query lists all triggers on a database.

Note: Additional filtering might be required.

SELECT 
     so.name AS trigger_name, 
     USER_NAME(os.uid) AS trigger_owner, 
     s.name AS table_schema, 
     OBJECT_NAME(parent_obj) AS table_name, 
     OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate, 
     OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete, 
     OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert, 
     OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter,
     OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof,
     OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects so 
    INNER JOIN sysusers su
        ON so.uid = su.uid 
    INNER JOIN sys.tables t 
        ON so.parent_obj = t.object_id 
    INNER JOIN sys.schemas s 
        ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR';

Query: List all Constraints

This sample query lists all constraints on a database.

Note: Additional filtering might be required.

SELECT con.[name] AS constraint_name,
    schema_name(t.schema_id) + '.' + t.[name] AS [table],
    col.[name] AS column_name,
    con.[definition],
    CASE WHEN con.is_disabled = 0 THEN 'Active' ELSE 'Disabled' END AS [status]
FROM sys.check_constraints con
    LEFT OUTER JOIN sys.objects t
        ON con.parent_object_id = t.object_id
    LEFT OUTER JOIN sys.all_columns col
        ON con.parent_column_id = col.column_id
        AND con.parent_object_id = col.object_id
ORDER BY con.name;

Query: List all constraints: PK, UK, FK, Check, and Default 

This sample query lists all Primary Key, Unique Keys, and other constraints on a database.

Note: Additional filtering might be required.

SELECT table_view,
    object_type, 
    constraint_type,
    constraint_name,
    details
FROM (
    SELECT schema_name(t.schema_id) + '.' + t.[name] as table_view, 
        case when t.[type] = 'U' then 'Table'
            when t.[type] = 'V' then 'View'
            end as [object_type],
        case when c.[type] = 'PK' then 'Primary key'
            when c.[type] = 'UQ' then 'Unique constraint'
            when i.[type] = 1 then 'Unique clustered index'
            when i.type = 2 then 'Unique index'
            end as constraint_type, 
        isnull(c.[name], i.[name]) as constraint_name,
        substring(column_names, 1, len(column_names)-1) as [details]
    FROM sys.objects t
        left outer join sys.indexes i
            on t.object_id = i.object_id
        left outer join sys.key_constraints c
            on i.object_id = c.parent_object_id 
            AND i.index_id = c.unique_index_id
       cross apply (select col.[name] + ', '
                        from sys.index_columns ic
                            inner join sys.columns col
                                on ic.object_id = col.object_id
                                and ic.column_id = col.column_id
                        where ic.object_id = t.object_id
                            AND ic.index_id = i.index_id
                                order by col.column_id
                                for xml path ('') ) D (column_names)
    WHERE is_unique = 1
        AND t.is_ms_shipped <> 1
    UNION ALLl 
    SELECT schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
        'Table',
        'Foreign key',
        fk.name as fk_constraint_name,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name
    FROM sys.foreign_keys fk
        inner join sys.tables fk_tab
            on fk_tab.object_id = fk.parent_object_id
        inner join sys.tables pk_tab
            on pk_tab.object_id = fk.referenced_object_id
        inner join sys.foreign_key_columns fk_cols
            on fk_cols.constraint_object_id = fk.object_id
    UNION ALL
    SELECT schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Check constraint',
        con.[name] as constraint_name,
        con.[definition]
    FROM sys.check_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            AND con.parent_object_id = col.object_id
    UNION ALL
    SELECT schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Default constraint',
        con.[name],
        col.[name] + ' = ' + con.[definition]
    FROM sys.default_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id) a
ORDER BY table_view, constraint_type, constraint_name;

Query: List all Indexes 

This sample query lists all Primary Indexes on a database.

Note: Additional filtering might be required.

SELECT i.[name] as index_name,
    substring(column_names, 1, len(column_names)-1) as [columns],
    case when i.[type] = 1 then 'Clustered index'
        when i.[type] = 2 then 'Nonclustered unique index'
        when i.[type] = 3 then 'XML index'
        when i.[type] = 4 then 'Spatial index'
        when i.[type] = 5 then 'Clustered columnstore index'
        when i.[type] = 6 then 'Nonclustered columnstore index'
        when i.[type] = 7 then 'Nonclustered hash index'
        end as index_type,
    case when i.is_unique = 1 then 'Unique'
        else 'Not unique' end as [unique],
    schema_name(t.schema_id) + '.' + t.[name] as table_view, 
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
        end as [object_type]
FROM sys.objects t
    inner join sys.indexes i
        on t.object_id = i.object_id
    cross apply (select col.[name] + ', '
                    from sys.index_columns ic
                        inner join sys.columns col
                            on ic.object_id = col.object_id
                            and ic.column_id = col.column_id
                    where ic.object_id = t.object_id
                        AND ic.index_id = i.index_id
                            order by key_ordinal
                            for xml path ('') ) D (column_names)
WHERE t.is_ms_shipped <> 1
    AND index_id > 0
ORDER BY i.[name];

Related Articles

• Profile Data in Database Column (KBA5846)