Dropping unnamed CONSTRAINTS in SQL Server

As maintainers of a number of client databases we come across issues in automating updates and changes to their SQL Server database schemas.  One such issue revolves around the automatic naming of CONSTRAINTS.

If you create a CONSTRAINT, such as a Primary Key, Foreign Key or even a Default without explicitly givin a name, SQL will generate its own unique name for the CONSTRAINT.

This is handy since its a unique name in the database, but its also unique across all databases so for clients with a hosted application, automated upgrades to each application database can be tricky.

This script below is a generic way of finding the constraint on a table column and removing it. Additional filtering can be applied to check for a format; For example, a Default CONSTRAINT in SQL 2005 uses DF__TheTable__TheCol__13D49E42

DECLARE @constraint sysname
SELECT
@constraint = object_name(default_object_id)
FROM
sys.columns
WHERE
object_id = object_id('[dbo].[]')
AND
name = ''
-- Add additional criteria here if required
IF len(@constraint)>0
BEGIN
EXEC ('ALTER TABLE [] DROP CONSTRAINT ' + @constraint)
END
ELSE
BEGIN
Print ('No CONSTRAINT found')
END