Drop Constraint in MS SQL that has an unknown name

Now when I say unknown name I catually mean I know constraint I want to drop, however the name of the constraint was automatically generated by SQL Server and that name contains a random number e.g.

"DF__user__15502E78"

Well I could just write a script like this to get rid of it:

alter table [user]
drop constraint [DF__user__15502E78]

Fine that works, but what if I have created multiple version of that DB on different servers, each time SQL Server creates that constraint for me automatically it will contain a different random number in the name. So now when I run the script I worte above on a different server it fails.

So I wrote this little script below, that will search out the constraint and drop it for me. Now you will have to tweak this to get it to work for you as you may have more constraints as I ad in my DB, so you will need to alter the WHERE clause a bit to just get back the constraint name you are looking for.

-- Because this constraint is dyncamically created by SQL Server
-- the name of the constraint contains a random number
-- so the name will be different from DB to DB.
-- So the only way to drop the constraint is to use the script below.
DECLARE @constraint varchar(100)
SET @constraint = (SELECT OBJECT_NAME(OBJECT_ID) --AS NameofConstraint,
            --SCHEMA_NAME(schema_id) AS SchemaName,
            --OBJECT_NAME(parent_object_id) AS TableName,
            --type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
    AND OBJECT_NAME(parent_object_id) = 'user'
    AND type_desc <> 'PRIMARY_KEY_CONSTRAINT')

print @constraint

declare @sqlscript varchar(200)

set @sqlscript = 'alter table [user] drop constraint ' + @constraint

print @sqlscript

exec(@sqlscript)

If you look at the select clause in the script above that is what i'm using to return the name of the constraint, you can uncomment the values in that select statement and just run and tweak that to get it just right for you and then insert it back into the rest of the script and your all sorted.