I just had a situation in which we are automating the comparing of databases and I came across a bunch of Defaults that were never explicitly named. This causes them to get a new name each time you create them and makes it much more difficult to write future update scripts when you don’t know the actual name it will be. In any case I decided to clean them up and since there were about a hundred I didn’t want to create all the cleanup code by hand and wrote a little script to generate the DROP & ADD’s which I figured others may find useful as well. The code would look much better if Windows Live Writer would keep the rich text when pasting into it but it doesn’t by default. There are some plug in’s to allow this but I have had little success so far in getting ones to install that actually do what I want. I will just have to deal with that later when I have more time.
The code below simply adds the necessary meta-data to the temp table that I need to Drop and Add the default constraints. I then run a select statement that dynamically builds each Drop or Add using that data and the resulting text can be used as a script to do the cleanup. It’s pretty simple stuff but may save some typing for anyone else wishing to cleanup messes like this that lazy developers :) and SSMS create. In my opinion ALL objects should be explicitly named at the time of initial creation and nothing should be left to be auto-generated by the tool or the engine. It will only lead to heartache down the road. And as always, test any code you find here first…
SET NOCOUNT ON ;
CREATE TABLE #DF ([Table Name] NVARCHAR(128), [Column Name] NVARCHAR(128),
[Default Name] NVARCHAR(128), [definition] NVARCHAR(200)) ;
INSERT INTO #DF ([Table Name], [Column Name], [Default Name], [definition] )
SELECT object_name(d.[parent_object_id]) AS [Table Name], c.[name] AS [Column Name],
d.[name] AS [Default Name], d.[definition]
FROM sys.columns AS c INNER JOIN sys.default_constraints AS d
ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
AND d.is_system_named = 1
-- Drop the existing constraints
SELECT 'DECLARE @DF_Name NVARCHAR(128) ;'
SELECT N'SELECT @DF_Name = d.[name] from sys.columns AS c INNER JOIN sys.default_constraints AS d
ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
WHERE c.[object_id] = object_id(''' + [Table Name] + N''') AND c.[name] = N''' + [Column Name] + N''' ;
IF @DF_Name IS NOT NULL
BEGIN
EXEC(''ALTER TABLE [dbo].[' + [Table Name] + N'] DROP CONSTRAINT ['' + @DF_Name + '']'') ;
END ;
'
FROM #DF
ORDER BY [Table Name];
-- Create the New constraints
SELECT N'IF NOT EXISTS(SELECT * FROM sys.columns AS c INNER JOIN sys.default_constraints AS d
ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
WHERE c.[name] = ''' + [Column Name] + N''' AND OBJECT_NAME(d.[parent_object_id]) = ''' + [Table Name] + N''')
BEGIN
ALTER TABLE [' + [Table Name] + N'] ADD CONSTRAINT [DF_' + [Table Name] + N'_'
+ [Column Name] + N'] DEFAULT ' + [definition] + N'FOR [' + [Column Name] + N'] ;
END ;
'
FROM #DF
ORDER BY [Table Name];