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