Stored Procedure

sp_helpconstraint Stored Procedure in SQL Server

Hi friends, today in SQL Server system stored procedure you 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

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

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s