批量设置BIT类型列默认值为0并设为非空（适配EF&C#迁移）

我正在将旧VB.NET代码迁移至Entity Framework（EF）与C#，需要对多个数据库执行统一修改：将所有可空BIT类型列设置默认值为0，并添加非空约束。我编写了两段SQL脚本，测试后功能可用，但不确定是否为最优方案，特此咨询。

第一步：删除名称以_dta_stat开头的统计信息 #

/*
Drop all Statistics
*/
DECLARE @TableName SYSNAME; 
DECLARE @StatName SYSNAME;

DECLARE StatCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT 
    t.name,
    s.name 
FROM 
    sys.tables t
JOIN 
    sys.stats s ON t.object_id = s.object_id
WHERE 
    s.name LIKE '_dta_stat%';

OPEN StatCursor;

FETCH NEXT FROM StatCursor INTO @TableName, @StatName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Drop the statistic
    EXEC ('DROP STATISTICS '+ @TableName +'.'+ @StatName+';')

    FETCH NEXT FROM StatCursor INTO @TableName, @StatName;
END;

CLOSE StatCursor;
DEALLOCATE StatCursor;

第二步：批量生成BIT列修改语句 #

/*
To Change all of the bit columns to be not NULL and have a default of 0
*/

declare @SQL nvarchar(max)
DECLARE @dbName nvarchar(50)
declare @schema nvarchar(50)
DECLARE @tableName VARCHAR(128)
DECLARE @colname VARCHAR(128)

DECLARE [bit] CURSOR FOR

SELECT QUOTENAME(db.name)
        ,QUOTENAME(OBJECT_SCHEMA_NAME(c.[object_id]))
        , QUOTENAME(t.name)
        , QUOTENAME(c.name)
FROM 
    sys.columns c
JOIN 
    sys.tables t ON c.object_id = t.object_id
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
JOIN 
    sys.databases db ON t.object_id = OBJECT_ID(db.name + '.' + s.name + '.' + t.name)
WHERE db.database_id > 5
    AND c.system_type_id = 104 AND c.is_nullable = 1
    AND t.is_ms_shipped = 0

OPEN [bit]

FETCH NEXT FROM [bit] INTO @dbName, @schema, @tableName, @colname

WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT 'UPDATE ' + @dbName + '.' + @schema + '.' + @tableName + ' SET ' + @colname + '=0 WHERE ' + @colname + ' IS NULL;'
        PRINT 'GO'
        PRINT 'ALTER TABLE ' + @dbName + '.' + @schema + '.' + @tableName + ' ADD CONSTRAINT ' + replace(replace(@colname,'[',''),']','') + '_FalseByDefault DEFAULT (0) FOR ' + @colname + ';'
        PRINT 'GO'
        PRINT 'ALTER TABLE ' + @dbName + '.' + @schema + '.' + @tableName + ' ALTER COLUMN ' + @colname + ' BIT NOT NULL;'
        PRINT 'GO'

        FETCH NEXT FROM [bit] INTO @dbName, @schema, @tableName, @colname
END

CLOSE           [bit]
DEALLOCATE      [bit]

GO

优化建议 #

针对统计信息删除脚本 #

• 改用sys.sp_executesql执行动态SQL，避免潜在的注入风险（即使当前场景风险极低，也能养成规范的编码习惯）：
  将原执行语句替换为：

  DECLARE @DropStatSQL NVARCHAR(MAX) = N'DROP STATISTICS ' + QUOTENAME(@TableName) + N'.' + QUOTENAME(@StatName);
  EXEC sys.sp_executesql @DropStatSQL;
  

• 可选：添加统计信息存在性校验，不过游标查询结果本身就是存在的统计信息，这步可省略。

针对BIT列修改脚本 #

1. 优化跨库查询逻辑
   原脚本通过JOIN sys.databases关联跨库对象，在权限受限场景下可能报错。建议改用sp_MSforeachdb遍历每个数据库，在目标库内执行查询，兼容性更好：

EXEC sp_MSforeachdb '
USE [?];
IF DB_ID(''?'') > 5
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT 
            QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)),
            QUOTENAME(t.name),
            QUOTENAME(c.name)
        FROM sys.columns c
        JOIN sys.tables t ON c.object_id = t.object_id
        WHERE c.system_type_id = 104 AND c.is_nullable = 1 AND t.is_ms_shipped = 0;
    
    OPEN cur;
    FETCH NEXT FROM cur INTO @schema, @tableName, @colname;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = N''UPDATE '' + @schema + N''.'' + @tableName + N'' SET '' + @colname + N'' = 0 WHERE '' + @colname + N'' IS NULL;
        ALTER TABLE '' + @schema + N''.'' + @tableName + N'' ADD CONSTRAINT DF_'' + SUBSTRING(@colname, 2, LEN(@colname)-2) + N''_False DEFAULT (0) FOR '' + @colname + N'';
        ALTER TABLE '' + @schema + N''.'' + @tableName + N'' ALTER COLUMN '' + @colname + N'' BIT NOT NULL;'';
        PRINT @SQL;
        -- EXEC sys.sp_executesql @SQL; -- 测试通过后取消注释执行
        FETCH NEXT FROM cur INTO @schema, @tableName, @colname;
    END;
    CLOSE cur;
    DEALLOCATE cur;
END
';

2. 约束命名更高效规范
   原脚本通过两次REPLACE去掉列名的括号，改用SUBSTRING提取括号内的列名更高效，同时给约束加上DF_前缀，符合SQL Server默认约束的命名规范：

N''CONSTRAINT DF_'' + SUBSTRING(@colname, 2, LEN(@colname)-2) + N''_False DEFAULT (0) FOR '' + @colname

3. 添加事务保证原子性
   如果要确保每个表的三步操作（UPDATE→添加默认约束→修改列非空）不会中途出错导致状态不一致，可以将每个表的操作包裹在事务中：

SET @SQL = N''BEGIN TRANSACTION;
BEGIN TRY
    UPDATE '' + @schema + N''.'' + @tableName + N'' SET '' + @colname + N'' = 0 WHERE '' + @colname + N'' IS NULL;
    ALTER TABLE '' + @schema + N''.'' + @tableName + N'' ADD CONSTRAINT DF_'' + SUBSTRING(@colname, 2, LEN(@colname)-2) + N''_False DEFAULT (0) FOR '' + @colname + N'';
    ALTER TABLE '' + @schema + N''.'' + @tableName + N'' ALTER COLUMN '' + @colname + N'' BIT NOT NULL;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH;'';

4. 大表分批更新优化
   如果涉及数据量极大的表，直接执行UPDATE可能导致长时间锁表，影响业务。建议改成分批更新，示例如下：

SET @SQL = N''DECLARE @RowCount INT = 1;
WHILE @RowCount > 0
BEGIN
    UPDATE TOP(1000) '' + @schema + N''.'' + @tableName + N'' 
    SET '' + @colname + N'' = 0 
    WHERE '' + @colname + N'' IS NULL;
    SET @RowCount = @@ROWCOUNT;
END;'';

内容的提问来源于stack exchange，提问作者robinhood1995