sql - 以编程方式重命名所有系统名称约束

Question

我有一个分布在许多客户站点的分布式应用程序。以前的开发人员不太擅长为约束提供用户定义的名称。我怎样才能将所有约束重命名为“DF_[TableName]_[ColumnName]”之类的名称？我可以尝试将光标放在表格列表上。如果我将其部署到客户站点，您认为这会成为问题吗？还是有更好的方法来实现这一点？

SELECT sys.schemas.name as [Schema],
sys.tables.name as [TableName],
sys.all_columns.name as [Column],
  default_constraints.name as [Constraint]
  FROM
    sys.all_columns
       INNER JOIN sys.tables
        ON all_columns.object_id = tables.object_id
       INNER JOIN sys.schemas
        ON tables.schema_id = schemas.schema_id
       INNER JOIN sys.default_constraints
        ON all_columns.default_object_id = default_constraints.object_id
       where default_constraints.name like '%[0-9]%'

Answer 1

如果您为每个约束类型使用命名约定，那么这会对您有所帮助。在这里，我举了一个例子来说明如何使用默认约束来处理这个问题。该过程将找到默认约束，删除那些不遵循命名约定的约束，并使用旧的定义创建新约束。

这是一次运行的简单版本

 DECLARE @SchemaName sysname = 'dbo';
 DECLARE @TableName sysname = NULL;
 DECLARE @ColumnName sysname = NULL;
 DECLARE @sql VARCHAR(max) = '';

SELECT
@sql += 
  'ALTER TABLE [' + s.name + '].[' + o.name + '] DROP CONSTRAINT [' + dc.name + ']; ' + 
  'ALTER TABLE [' + s.name + '].[' + o.name + '] ADD  CONSTRAINT [DF_' + o.name + '_' + c.name + '] DEFAULT ' + dc.DEFINITION + ' FOR [' + c.name + '];'

  FROM dbo.sysobjects do
        INNER JOIN dbo.sysobjects o ON do.parent_obj = o.id
        INNER JOIN sys.default_constraints dc ON dc.object_id = do.id
        INNER JOIN sys.columns c ON c.object_id = o.id
                   AND c.column_id = dc.parent_column_id
        INNER JOIN sys.schemas s ON s.schema_id = dc.schema_id
  WHERE o.type = 'U' 
       AND do.type = 'D'
       AND s.name = @SchemaName
       AND o.name = CASE WHEN ISNULL(@TableName,'') = '' 
                        THEN o.name 
                        ELSE @TableName END
       AND c.name = CASE WHEN ISNULL(@ColumnName,'') = ''
                         THEN c.name 
                         ELSE @ColumnName END
       AND do.name NOT LIKE 'DF_' + o.name + '_' + c.name
  ORDER BY o.name
 --PRINT (@sql)
  EXECUTE(@sql)

这是一个程序，以防您想每隔一段时间继续运行该程序

USE [YourDatabaseName]
    GO
    -- Check if the procedure exists and drop it if so
    IF  EXISTS (SELECT * 
                FROM sys.objects 
                WHERE object_id = OBJECT_ID(N'[dbo].[usp_NamingConventionDefaultConstraint]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[usp_NamingConventionDefaultConstraint]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --procedure can be used for specific schema/table/columns or those can be left null to include everything. 
    CREATE PROC [dbo].[usp_NamingConventionDefaultConstraint]
                @SchemaName sysname = 'dbo',
                @TableName sysname = NULL,
                @ColumnName sysname = NULL
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @sql VARCHAR(max)

        DECLARE GetIndexes CURSOR
        FOR
            SELECT
 --here drop the old constraint and create a new one with the old's definition
            'ALTER TABLE [' + s.name + '].[' + o.name + '] DROP CONSTRAINT [' + dc.name + ']; ' + 
            'ALTER TABLE [' + s.name + '].[' + o.name + '] ADD  CONSTRAINT [DF_' + o.name + '_' + c.name + '] DEFAULT ' + dc.DEFINITION + ' FOR [' + c.name + '];'
            AS dc_alter
            FROM dbo.sysobjects do
            INNER JOIN dbo.sysobjects o ON do.parent_obj = o.id
            INNER JOIN sys.default_constraints dc ON dc.object_id = do.id
            INNER JOIN sys.columns c ON c.object_id = o.id 
                                     AND c.column_id = dc.parent_column_id
            INNER JOIN sys.schemas s ON s.schema_id = dc.schema_id
            WHERE o.type = 'U' 
            -- work only on default constraints 
            AND do.type = 'D'
            AND s.name = @SchemaName
            AND o.name = CASE WHEN ISNULL(@TableName,'') = '' THEN o.name ELSE @TableName END
            AND c.name = CASE WHEN ISNULL(@ColumnName,'') = '' THEN c.name ELSE @ColumnName END
            -- here goes the naming convention you have in mind
            --DF_TableName_ColumnName
            AND do.name NOT LIKE 'DF_' + o.name + '_' + c.name
            ORDER BY o.name

        OPEN GetIndexes

        FETCH NEXT FROM GetIndexes
        INTO @sql   

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF  ISNULL(@sql,'')<>''
            BEGIN
                EXEC (@sql)
            END

            FETCH NEXT FROM GetIndexes
            INTO @sql   
        END

        CLOSE   GetIndexes
        DEALLOCATE  GetIndexes

    END
    GO