sp_helpconstraint – Day 5 – SQL Server System Stored Procedure

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:

sp_helpconstraint [ @objname = ] 'table' 
     [ , [ @nomsg = ] 'no_message' ]

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:

CREATE TABLE dbo.Person
(
	PersonId INT NOT NULL,
	PersonName VARCHAR(25) NOT NULL,
	Country VARCHAR(10),
	DateAdded DATETIME CONSTRAINT DF_DateAdded DEFAULT GETDATE(),
	CONSTRAINT PK_Person_PersonID PRIMARY KEY CLUSTERED (PersonId)
)

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

Let’s execute the execute procedure –

EXEC sp_helpconstraint 'Person'

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