THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

Cleaning up Un-Named Defaults

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];

 

Published Friday, June 26, 2009 1:07 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Armando Prato said:

The moral of the story here:  Always name all your constraints.

June 26, 2009 1:15 PM
 

Brandon Turner [MSFT] said:

To keep the formatting in Windows Live Writer try doing Control+Shift+V (Special Paste).

June 26, 2009 4:33 PM
 

Andrew Kelly said:

First thing I tried but the special paste (for me at least) only allows HTML or Plain text, nothing for regular Rich Text.

June 26, 2009 4:39 PM
 

Hugo Kornelis said:

Hi Andrew,

My version of Live Writer (a beta version dating back to 2006 or so) does preserve formatting if I copy text from a Word document. Since I discovered this, I always wrote my blog posts in Word (since copying code snippets to Word does preserve formatting as well), and then copy the finished post from Word to LiveWriter.

June 28, 2009 3:57 AM
 

Hugo Kornelis said:

Note to self: Next time, read ALL new blog posts before commenting...

June 28, 2009 4:06 AM
 

Andrew Kelly said:

Hugo,

And commenting on the correct ones helps too :)...

June 28, 2009 2:13 PM
 

MontyMole said:

Rather than doing a DROP and ADD, why don't you use sp_rename ?

June 29, 2009 5:07 AM
 

Andrew Kelly said:

Monty,

I guess you could rename them but sicne these are just table defaults I don't see much performance difference if any either way. I have had some problems with renaming certain objects in the past so I use it sparingly. It was a long time ago and may have just been a bug that has since been fixed.

June 29, 2009 9:13 AM
 

Michael K. Campbell said:

Yeah - I would have gone DROP/ADD instead of sp_rename for just the same reason. Potentially just a bit of 'superstition' on my part - but I like the cleaner approach that DROP/ADD gives with constraints.

And yeah, +1 on the best practice of always naming your constraints.

July 16, 2009 6:55 PM
 

Andres Bueno said:

It did not drop the existing constrains, but the create scripts worked fine, thanks.

April 13, 2010 3:37 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement