Alter "Default" in SQL Server

Question

I have custom default value in a SQL server 2008 table.

Similar to this

CREATE DEFAULT [dbo].[Default_Timestamp]
AS 
   GetDate()

Now I want to change the value in that default.

CREATE DEFAULT [dbo].[Default_Timestamp]
AS 
   GETUTCDATE()

Before I can edit the existing one, I need to drop the first one and recreate it.

DROP DEFAULT [dbo].[Default_Timestamp]

it gives following error.

Msg 3716, Level 16, State 3, Line 4
The default 'dbo.Default_Timestamp' cannot be dropped because it is bound to one or more column.

Since the default is already use by few tables I cannot drop and recreate a new one.

I know I need to unbind all the tables from this default before I can recreate it.

Can anyone provide a script to list all the table and columns which are bound with that default?

Answer 1

It's a multi-step process:

1) Find the object_id for your default :

DECLARE @DefaultObjectID INT
SELECT @DefaultObjectID = OBJECT_ID('Default_Timestamp')

2) Find all the columns that reference that default:

SELECT 
    ColumnName = c.Name,
    TableName = t.Name,
    UnbindCmd = 'EXEC sp_unbindefault ''' + t.Name + '.' + c.name + ''''
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE default_object_id = @DefaultObjectID

This will produce a list of UnbindCmd commands to actually remove the DEFAULT from those columns.

3) Now, copy that column from your SQL Server Mgmt Studio window, and execute it in a new query window to actually "unbind" the default from all those columns

4) Now define your new default

However: these days, I would probably not define a new DEFAULT per se - can't you just set the default constraint on the columns in question directly?

ALTER TABLE dbo.YourTable
ADD CONSTRAINT DF_YourTable_TimeStamp
DEFAULT GETUTCDATE() FOR YourColumnName

Seems a like easier to work with going into the future! When you explicitly name your constraint, you can also easily find and drop that constraint again, if need be.

Answer 2

I ran across this issue when I found some old style defaults in my database where I had to update uses_ansi_nulls and uses_quoted_identifier en mass in my database.

Here is the code that I ended up writing to replace the old style defaults (CREATE DEFAULT sp_BindDefault) with the newer style (ALTER TABLE).

DECLARE @strSQL varchar(max)
        , @DBName varchar(50) = DB_NAME();

DROP TABLE IF EXISTS #tmp;

SELECT  IDENTITY(int, 1,1) AS ID,
        UnbindCmd = 'EXEC sp_unbindefault ''' + t.Name + '.' + c.name + ''';',
        DropCmd = 'DROP DEFAULT IF EXISTS [' + SCHEMA_NAME(SO.schema_id) + '].[' + OBJECT_NAME(SO.object_id) + '];',
        CreateCmd = 'ALTER TABLE [' + SCHEMA_NAME(t.Schema_id) + '].[' + t.Name + ']'
                    + ' ADD CONSTRAINT df_' + REPLACE(t.Name, ' ', '') + '_' + REPLACE(c.Name, ' ', '')
                    + ' DEFAULT (' + RIGHT(m.definition, LEN(m.definition) - CHARINDEX(' AS ', m.definition) - 3)
                    + ') FOR ' + QUOTENAME(c.Name) + ';'
INTO    #tmp
FROM    sys.sql_modules m
        join sys.columns c on c.default_object_id = m.object_id
        INNER JOIN sys.tables t ON c.object_id = t.object_id
        JOIN sys.objects SO ON SO.object_id = C.default_object_id
WHERE   (m.uses_ansi_nulls = 0 
        OR m.uses_quoted_identifier = 0)
ORDER BY
        t.name
        , c.name;

BEGIN TRANSACTION
    BEGIN TRY
        DECLARE UnbindCursor CURSOR LOCAL FAST_FORWARD
        FOR SELECT  UnbindCmd
            FROM    #tmp
            ORDER BY
                    ID;

        OPEN UnbindCursor;

        FETCH NEXT FROM UnbindCursor INTO @strSQL;

        WHILE @@FETCH_STATUS = 0
            BEGIN
                EXEC (@strSQL);

                FETCH NEXT FROM UnbindCursor INTO @strSQL;
            END

        CLOSE UnbindCursor;
        DEALLOCATE UnbindCursor;

        DECLARE DropCursor CURSOR LOCAL FAST_FORWARD
        FOR SELECT  DropCmd
            FROM    #tmp
            GROUP BY
                    DropCmd
            ORDER BY
                    MIN(id)

        OPEN DropCursor;

        FETCH NEXT FROM DropCursor INTO @strSQL;

        WHILE @@FETCH_STATUS = 0
            BEGIN
                EXEC (@strSQL);

                FETCH NEXT FROM DropCursor INTO @strSQL;
            END

        CLOSE DropCursor;
        DEALLOCATE DropCursor;

        DECLARE CreateCursor CURSOR LOCAL FAST_FORWARD
        FOR SELECT  CreateCmd
            FROM    #tmp
            ORDER BY
                    id

        OPEN CreateCursor;

        FETCH NEXT FROM CreateCursor INTO @strSQL;

        WHILE @@FETCH_STATUS = 0
            BEGIN
                EXEC (@strSQL);

                FETCH NEXT FROM CreateCursor INTO @strSQL;
            END

        CLOSE CreateCursor;
        DEALLOCATE CreateCursor;

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage nvarchar(4000)
                , @ErrorSeverity int
                , @ErrorState int;

        SELECT   
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),  
            @ErrorState = ERROR_STATE();  

        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

        RAISERROR (@ErrorMessage, -- Message text.  
                    @ErrorSeverity, -- Severity.  
                    @ErrorState -- State.  
                    );  
    END CATCH