在SQL Server中修改“默认值”

Question

我在SQL Server 2008表中设置了自定义的默认值。

类似于这样：

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

现在我想更改默认值中的数值。

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

在我可以编辑现有的内容之前，我需要删除第一个并重新创建它。

DROP DEFAULT [dbo].[Default_Timestamp]

这里出现了以下错误:

Msg 3716, Level 16, State 3, Line 4
默认值'dbo.Default_Timestamp'无法删除，因为它已绑定到一个或多个列。

由于该默认值已经被多个表所使用，因此我无法删除并重新创建新的默认值。

我知道在重新创建之前需要解除所有使用该默认值的表的绑定。

是否有人能够提供一个脚本来列出所有与该默认值绑定的表和列？

- LazyProgrammer

Answer 1

这是一个多步骤的过程：

1）找到您默认对象的object_id：

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

2）查找所有引用该默认值的列：

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

这将生成一个UnbindCmd命令列表，以实际从这些列中删除DEFAULT。

3）现在，从SQL Server Mgmt Studio窗口复制该列，并在新的查询窗口中执行它，以实际“解除绑定”所有这些列上的默认值。

4）现在定义您的新默认值。

然而：最近，我可能不会定义一个新的DEFAULT - 您不能直接在相关列上设置默认约束吗？

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

看起来更容易适应未来的工作！当您明确命名约束时，如果需要，您也可以轻松地找到并删除该约束。

- marc_s

Answer 2

我在数据库中发现了一些旧风格的默认设置，需要批量更新uses_ansi_nulls和uses_quoted_identifier。以下是我编写的代码，以替换旧风格的默认设置（CREATE DEFAULT sp_BindDefault）为更新的样式（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

- Scott Coutant

Answer 3

我从未见过CREATE DEFAULT或DROP DEFAULT作为独立的命令，但只知道使用CONSTRAINT子句来添加或删除默认约束。

SSMS 2008生成以下代码（在表设计器中右键单击，“生成更改脚本…”）：

ALTER TABLE dbo.T ADD CONSTRAINT
    DF_T_DateTimeColumn DEFAULT getdate() FOR DateTimeColumn
GO

ALTER TABLE dbo.T
    DROP CONSTRAINT DF_T_DateTimeColumn
GO
ALTER TABLE dbo.T ADD CONSTRAINT
    DF_T_DateTimeColumn DEFAULT getutcdate() FOR DateTimeColumn
GO

更新：

我承认，我从未使用过 EXEC sp_bindefault，但我认为它的使用是不被鼓励的，因为MSDN上说：

此功能将在未来的 Microsoft SQL Server 版本中删除。不要在新开发工作中使用此功能，并尽快修改当前使用此功能的应用程序。我们建议您使用 ALTER TABLE 或 CREATE TABLE 语句的 DEFAULT 关键字创建默认定义。

当然，这也适用于 sp_unbindefault。

- devio

Answer 4

您可以通过查看sys.columns中的default_object_id来找到使用默认对象的列：

默认对象的ID，无论它是独立对象sys.sp_bindefault还是内联的列级DEFAULT约束。

了解这一点很容易构建一个脚本，将所有列从默认对象中解绑，并用基于约束的默认值替换默认值：

use master;
go

if db_id('test') is not null
    drop database test;
go

create database test;
go

use test;
go

create default foo as 1;
go

create table t1 (a int);
create table t2 (b int);
go

exec sp_bindefault 'foo', 't1.a';
exec sp_bindefault 'foo', 't2.b';
go

drop default foo;
-- Msg 3716, Level 16, State 3, Line 2
-- The default 'foo' cannot be dropped because it is bound to one or more column.
go

declare crs cursor static forward_only read_only for
select object_schema_name(object_id) as schema_name,
        object_name(object_id) as object_name,
        name as column_name
    from sys.columns where default_object_id = object_id('foo');
open crs;

declare @schema_name sysname, @object_name sysname, @column_name sysname, @sql nvarchar(max);

fetch next from crs into @schema_name, @object_name, @column_name;
while @@fetch_status = 0
begin
    set @sql = N'exec sp_unbindefault ' + quotename(
        quotename(@schema_name) + N'.'+
        quotename(@object_name) + N'.'+
        quotename(@column_name), '''');
    print @sql;
    exec sp_executesql @sql;
    set @sql = N'alter table ' +
        quotename(@schema_name) + N'.' + 
        quotename(@object_name) + N' add constraint ' + 
        quotename(N'default_' + @column_name) + N' default 2 for ' +
        quotename(@column_name);
    print @sql;
    exec sp_executesql @sql;
    fetch next from crs into @schema_name, @object_name, @column_name;
end

close crs;
deallocate crs;
go

drop default foo;
-- it now succeeds
go

- Remus Rusanu