Checking for constraints in SQL Server

Ted

Ted

n n From time to time, you may find that you need to test for the existence of a SQL Server table constraint before you perform some action, such as dropping or altering a constraint or table. Below is a snippet I have found useful.


DECLARE @constraintName varchar(200);
SET @constraintName = 'SomeConstraintName';

IF ( SELECT count(*) FROM sys.objects 
WHERE type_desc LIKE '%CONSTRAINT'
AND OBJECT_NAME(object_id) = @constraintName) <> 0 BEGIN
PRINT 'FOUND Constraint: ' + @constraintName;
-- Perform some action...
END
ELSE
PRINT 'CANNOT FIND Constraint: ' + @constraintName;

BTW, I got the idea from this snippet and based it, on this StackOverflow article: https://stackoverflow.com/questions/2499332/how-to-check-if-a-constraint-exists-in-sql-server Enjoy!

Leave a comment

Leave a Reply

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

Comments