Hi friends, today in SQL Server system stored procedure series we will learn how to get information about constraints defined on a table using stored procedure sp_helpconstraint.

Sp_helpconstraint stored procedure lists all constraints defined on a table along with their user_defined or system-supplied name. It also lists all columns on which constraints are defined and expressions defined for DEFAULT and CHECK constraints.

Syntax of sp_helpconstraint is as follows:

Arguments

@objname is the table name on which constraints are defined having datatype nvarchar(776) with no default value.

@nomsg is an optional parameter that’s prints the table name.

Now, let’s create a table Person defining some constraints on it:

In table Person I have created two constraints DEFAULT on column DateAdded and PRIMARY KEY on column PersonId.

Let’s execute the execute procedure –

sp_helpconstraint

Sp_helpconstraint returns result set that includes following columns:

Constraint_Type – It shows the type of constraint (PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK, so on) defined on column.

Constraint_Name – It shows unique user_defined or system-supplied constraint name of a column.

Delete_action – It is applicable for FOREIGN KEY constraint with either cascade or No Action. It is cascade only when a FOREIGN KEY column has ON DELETE CASCADE rule.

Update_Action – It is applicable to FOREIGN KEY constraint with either cascade or No Action. It is cascade only when a FOREIGN KEY column has ON UPDATE CASCADE rule.

Status_Enabled – It indicates whether FOREGIN KEY or CHECK constraint is enabled or not.

Status_For_Replication – It indicates whether FOREIGN KEY or CHECK constraint is to be enforced during replication or not.

Constraint_Keys – It shows expression defined for CHECK and DEFAULT constraint, column name for other constraint.

That’s all folks for the day, I will continue with other system stored procedure in next blog.

Hope you like this post.

Regards,

Kapil Singh

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter