Skip to main content
Delphix

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

 

KBA

KBA# 5848

 

Sample queries for MS SQL Server 

This page provides sample MS SQL Server queries to collect details about: 

  • Triggers
  • Constraints
  • Primary Key, Unique Keys, and other Constraints
  • Indexes

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];

 

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0