I've created some foreign keys without an explicit name.
Then I've found SQL generated crazy names like FK__Machines__IdArt__760D22A7. Guess they will be generated with different names at different servers.
Is there any nice function to drop the unnamed FK constraints passing as arguments the tables and the fields in question?
For dropping an individual unnamed default constrain on a column use the following code:
DECLARE @ConstraintName VARCHAR(256) SET @ConstraintName = ( SELECT obj.name FROM sys.columns col LEFT OUTER JOIN sys.objects obj ON obj.object_id = col.default_object_id AND obj.type = 'F' WHERE col.object_id = OBJECT_ID('TableName') AND obj.name IS NOT NULL AND col.name = 'ColunmName' ) IF(@ConstraintName IS NOT NULL) BEGIN EXEC ('ALTER TABLE [TableName] DROP CONSTRAINT ['+@ConstraintName+']') END If you want to do this for a default column, which is probably more common than the original question and I'm sure a lot of people will land on this from a Google search, then just change the line:
obj.type = 'F' to
obj.type = 'D' 
There is not a built in procedure to accomplish this, but you can build your own using the information in the information_schema views.
Table based example
Create Proc dropFK(@TableName sysname) as Begin Declare @FK sysname Declare @SQL nvarchar(4000) Declare crsFK cursor for select tu.Constraint_Name from information_schema.constraint_table_usage TU LEFT JOIN SYSOBJECTS SO ON TU.Constraint_NAME = SO.NAME where xtype = 'F' and Table_Name = @TableName open crsFK fetch next from crsFK into @FK While (@@Fetch_Status = 0) Begin Set @SQL = 'Alter table ' + @TableName + ' Drop Constraint ' + @FK Print 'Dropping ' + @FK exec sp_executesql @SQL fetch next from crsFK into @FK End Close crsFK Deallocate crsFK End 
Although Gunner's answer puts people on the right track if you want to drop an actual DEFAULT constraint rather than an FKey constraint (which is what brought ME here too!) there are problems with it.
I think this fixes them all. (T-SQL)
CREATE PROC #DropDefaultConstraint @SchemaName sysname, @TableName sysname, @ColumnName sysname AS BEGIN DECLARE @ConstraintName sysname; SELECT @SchemaName = QUOTENAME(@SchemaName) , @TableName = QUOTENAME(@TableName); SELECT @ConstraintName = QUOTENAME(o.name) FROM sys.columns c JOIN sys.objects o ON o.object_id = c.default_object_id WHERE c.object_id = OBJECT_ID(@SchemaName+'.'+@TableName) AND c.name = @ColumnName; IF @ConstraintName IS NOT NULL EXEC ('ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName + ''); END This will let you drop a specific foreign key constraint based on tablename + column name
After trying out the other answers I just had a poke around in the system tables until I found something likely looking.
The one you want is Constraint_Column_Usage which according to the docs Returns one row for each column in the current database that has a constraint defined on the column.
I've joined it to sys.objects to just get foreign keys.
In a procedure (this borrows from the other answers. cheers guys!):
Create Proc yourSchema.dropFK(@SchemaName NVarChar(128), @TableName NVarChar(128), @ColumnName NVarChar(128)) as Begin DECLARE @ConstraintName nvarchar(128) SET @ConstraintName = ( select c.Constraint_Name from Information_Schema.Constraint_Column_usage c left join sys.objects o on o.name = c.Constraint_Name where c.TABLE_SCHEMA = @SchemaName and c.Table_name = @TableName and c.Column_Name = @ColumnName and o.type = 'F' ) exec ('alter table [' + @SchemaName + '].[' + @TableName + '] drop constraint [' + @ConstraintName + ']') End This will generate a script to rename default constraints to use the pattern DF__table_name__column_name
SELECT 'EXEC sp_rename ''dbo.' + dc.name + ''', ''DF__' + t.name + '__' + c.name + '''' AS the_script, t.name AS table_name, c.name AS column_name, dc.name AS old_constraint_name FROM sys.default_constraints dc INNER JOIN sys.tables t ON dc.parent_object_id = t.object_id INNER JOIN sys.columns c ON dc.parent_column_id = c.column_id AND t.object_id = c.object_id WHERE dc.name <> 'DF__' + t.name + '__' + c.name Neither of these worked for me so I had to come up with this to work on mssql server version both 12 and 14.
First, inspect the name given to the FK by the RDBMS, it has the same prefix and body but differs only in suffix hash.
Second, select names of these constraints.
Third, exec alter command that drops them.
Finally you can drop the column or table blocked by these
BEGIN TRANSACTION; DECLARE @ConstraintName nvarchar(200) SELECT @ConstraintName = name FROM sys.objects WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' AND name LIKE 'FK__table_col_shortcut1___%' EXEC('ALTER TABLE table1 DROP CONSTRAINT ' + @ConstraintName) SELECT @ConstraintName = name FROM sys.objects WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' AND name LIKE 'FK__table_col_shortcut2___%' EXEC('ALTER TABLE table2 DROP CONSTRAINT ' + @ConstraintName) SELECT @ConstraintName = name FROM sys.objects WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' AND name LIKE 'FK__table_col_shortcut3___%' EXEC('ALTER TABLE table3 DROP CONSTRAINT ' + @ConstraintName) DROP TABLE table_referenced; COMMIT TRANSACTION; Lesson learnt, I will always create constraints explicitly from now on!
