Listing Table Constraints with sp_helpconstraint
- Posted in:
- SQL Server
- TSQL
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