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' ]
@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.