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')
