December 11, 2015 | Article | No Comments
A constraint is defined as a specific rule applied to data in a table in such a way when the constraint is violated, the action is aborted. Constraint can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).
Many times when we use action like: INSERT, UPDATE, DELETE, we get error(s) because on or the other constraint is violated. It’s getting annoying when we are new to this database and don’t know what constraints are set on the tables.
In this article, we will discuss about how to list all the constraints in a database. All the methods given have been tested on SQL Server 2012.
Method 1: Using sys.objects
sys.objects is a special table which contains row for each user-defined, schema-scoped object that is created within a database. Including our constraint.
All we need to do is query that table.
-- To Display all the Constraints in the Database SELECT * FROM [sys].[objects] WHERE [type_desc] LIKE '%CONSTRAINT'
The above query will display all the fields of sys.objects. For convenience use, we can refine it to display in elegant way.
-- To Display all the Constraints in the Database SELECT OBJECT_NAME(object_id) AS [ConstraintName], SCHEMA_NAME(schema_id) AS [SchemaName], OBJECT_NAME(parent_object_id) AS [TableName], [type_desc] AS [ConstraintType] FROM [sys].[objects] WHERE [type_desc] LIKE '%CONSTRAINT'
We can also display all the constraints of a table, let say ‘NEST_User’, by querying:
-- To Display all the Constraints in table 'Nest_User' SELECT OBJECT_NAME(object_id) AS [ConstraintName], SCHEMA_NAME(schema_id) AS [SchemaName], [type_desc] AS [ConstraintType] FROM [sys].[objects] WHERE [type_desc] LIKE '%CONSTRAINT' AND OBJECT_NAME(parent_object_id)='NEST_User'
Method 2: Using INFORMATION_SCHEMA.TABLE_CONSTRAINTS
information schema view is one of several methods SQL Server provides for obtaining metadata. It provides an internal, system table-independent view of the SQL Server metadata. However, it has one shortcoming; it won’t display the default constraints of the database.
– To Display all the Constraints in the Database SELECT * FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] – To Display all the Constraints in table ‘NEST_User’ SELECT * FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [TABLE_NAME]='NEST_User'
To display DEFAULT constraint in the database we can use following SQL statement.
– To Display Default Constraints in Database SELECT OBJECT_NAME(PARENT_OBJECT_ID) AS [TABLE_NAME], [COL_NAME] (PARENT_OBJECT_ID, PARENT_COLUMN_ID) AS [COLUMN_NAME], [NAME] AS [DEFAULT_CONSTRAINT_NAME] FROM [SYS].[DEFAULT_CONSTRAINTS]database, sql server