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

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

Leave a Reply

Your email address will not be published. Required fields are marked *