List Indexes, Constraints, and Triggers on MS SQL Server (KBA5848)
KBA
KBA#5848
At a Glance
Summary: | This KBA provides sample queries to be used on SQL Server to collect details about:
|
---|---|
Applies to: | These procedures apply to SQL Server only (no specific Delphix version). |
More Info: | Details about the built-in feature for Drop Indexes, Disable Constraints, and Disable Triggers: |
Queries for MS SQL Server
This page provides sample MS SQL Server queries to list the following:
- List all Triggers
- List all Constraints
- List all Primary Keys, Unique Keys, and other Constraints
- List all other Indexes
Sample queries - List all
Triggers
This sample query lists all triggers on a database.
SELECT so.name AS trigger_name, USER_NAME(so.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 so.type = 'TR';
Constraints
This sample query lists all constraints on a database.
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;
Constraints PK, UK, FK, Check, and Default
This sample query lists all Primary Key, Unique Keys, and other constraints on a database.
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 ALL 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;
Indexes
This sample query lists all Primary Indexes on a database.
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 All All