Recently I was working on updating some tables, and the SQL that was generated for the update included some code that looked like this:
ALTER TABLE [schema].[table] DROP CONSTRAINT [DF__Table__Column__a789cv78s7s]
Well, it dawned on me that the various environments we have *COULD* have different constraint names.
Now, these constraints will be deleted when the table is dropped, so an explicit drop is not necessarily required.
However, if you actually name your default constraints, rather than utilize the shorthand in the table definition, then you may run into an issue of duplicate default constraint names.
This handy script will drop all of the defaults on a particular table.
DECLARE @SchemaName NVARCHAR(256), @TableName NVARCHAR(256), @SQL NVARCHAR(MAX), @NewLine CHAR(1) SELECT @SchemaName = N'dbo', @TableName = N'TheNameOfTheTable', @NewLine = CHAR(10) SELECT @SQL = ISNULL(@SQL + @NewLine, '') + 'ALTER TABLE [' + S.name + '].[' + T.name + '] ' + 'DROP CONSTRAINT [' + D.name + ']' FROM sys.tables T INNER JOIN sys.default_constraints D ON D.parent_object_id = T.object_id INNER JOIN sys.columns C ON C.object_id = T.object_id AND C.column_id = D.parent_column_id INNER JOIN sys.schemas S ON T.schema_id = S.schema_id WHERE S.name = @SchemaName AND T.name = @TableName PRINT @SQL EXECUTE (@SQL)
Leave a Reply