Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

Listing Table Constraints with sp_helpconstraint

When I'm in need of SQL Server metadata, the information schema views in the INFORMATION_SCHEMA schema are usually my go-to for queries. Like many views, I find them convenient. However, they may not be suitable in some situations. Here is one involving table constraints. Consider the following tables:

USE tempdb;

CREATE TABLE dbo.Vexide (
	VexideID INT NOT NULL PRIMARY KEY,
	Name VARCHAR(63)
)

CREATE TABLE dbo.Zomace (
	ZomaceID INT NOT NULL
		CONSTRAINT PK_Zomace PRIMARY KEY,
	VexideID INT NULL
		CONSTRAINT FK_Zomace_VexideID FOREIGN KEY REFERENCES dbo.Vexide(VexideID),
	RefDate DATE
		CONSTRAINT CK_Zomace_RefDate CHECK(RefDate > '20200101'),
	OrderDate DATETIME
		CONSTRAINT DF_Zomace_OrderDate DEFAULT(CURRENT_TIMESTAMP)
)

ALTER TABLE dbo.Zomace
ADD CONSTRAINT UQ_Zomace_VexideID_RefDATE
UNIQUE(VexideID, RefDate);

I wanted to get a list of names for all the constraints on table dbo.Zomace. Initially, the next query seemed to be just what I needed:

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'Zomace'

But after a second look, I noticed DEFAULT CONSTRAINT DF_Zomace_OrderDate was not returned by the query. The information schema views are designed to comply with the ISO standard definition for the INFORMATION_SCHEMA. Perhaps default constraints are not part of the standard? Fortunately, there is a built-in system stored procedure that returns a list of all constraint types: sp_helpconstraint. It returns constraint names, types, keys, and other information for a specified table (passed in as a stored procedure parameter).

EXEC sp_helpconstraint 
	@objname = 'dbo.Zomace', 
	@nomsg = NULL;

Comments

Post comment