How to check if a default constraint exists in SQL Server

Perhaps your scripting rollout and rollback DDL SQL changes and you want to check for instance if a default constraint exists before attemping to drop it and its parent column.
Most schema checks can be done using a collection of information schema views which SQL Server has built in. To check for example the existence of columns you can query the information schema view for columns with a query which looks something like:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourtablename' AND COLUMN_NAME = 'yourcolumnname')

however none of the information schema views have anything in them for checking for a named default constraint. For that you need to use something like:

IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE_DESC =  'DEFAULT_CONSTRAINT' AND NAME = 'yourconstraintname')

or:

IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE = 'D' AND NAME = 'yourconstraintname')

Leave a Reply

Your email address will not be published.