Xathrya Sabertooth

List All Constraints of Database or Table in SQL Server

Posted: 27 Feb 2014 04:23 PM PST

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]

Proyek Teknologi

Jumat, 28 Februari 2014