主页 / dba / 问题 / 56824
Accepted
DLA2014
Asked: 2014-01-16 05:10:37 +0800 CST

将表格转换为 Ansi 填充

  • 772

我目前正在使用 SQL Server 2008 R2 服务器和一个数据库，该数据库使用很多表，这些表是 ANSI_PADDED 设置的混合包。90% 的表具有启用 Ansi 填充的列和禁用 Ansi 填充的列。

我现在的任务是“转换”这些表，以便每一列都使用 Ansi_Padding ON。

我想知道最简单的方法是什么？

我应该编写一个脚本来“转换”一个表，但最后我需要一个脚本来更改所有表。

sql-server-2008-r2 sql-server-2012

2 个回答

  1. 这可能是一项艰巨的任务。我最初的想法是，您可以简单地为每OFF列添加一个新版本，复制数据，删除旧列，然后重命名新列。然而，有一大堆复杂的事情会让这变得乏味或完全不可能：

    • 需要禁用和/或删除/重新创建的约束（包括外键）
    • 计算列
    • 指向受影响表的索引视图和其他外围对象SCHEMABINDING
    • 阻塞这种类型的操作会在大表上造成
    • ALTER TABLE ADD考虑到在 2008 R2 中从元数据中导出正确的数据类型非常麻烦（2012 年更容易，但仍然没有野餐）

    老实说，我认为最简单的方法是生成整个数据库的脚本：

    • 右键单击数据库 > 任务 > 生成脚本
    • 如果您看到介绍屏幕，请选中复选框并单击下一步
    • 选择所有对象并单击下一步

    • 在“设置脚本选项”选项卡上，单击“高级”

      • 将“脚本的数据类型”更改为“模式和数据”

    • 在同一选项卡上，将输出更改为“新查询窗口”

    在生成的脚本中，在运行之前，将对原始数据库名称的所有引用更改为新的数据库名称（包括文件路径），并将ANSI_PADDING脚本中的所有设置更改为ON（使用查找/替换）。创建新数据库后，您可以重命名旧数据库并将新数据库重命名为旧名称。最终，一旦您确认一切正常，您就可以删除旧数据库。

    警告：不要在 SQL Server 2014 CTP2 中尝试这个 - 在回答这个问题的过程中，由于我刚刚提交的这个错误，我丢失了一些自己的工作。

    • 5
  2. Best Answer

    事实证明，如果您更改列，它将采用命令的ANSI_PADDING设置ALTER。这是我做的一个快速测试。

    SET ANSI_PADDING ON 
GO
CREATE TABLE ansi_test
    (Col1 varchar(10),
    Col2 varchar(10),
    Col3 varchar(10)
    )
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE ansi_test ADD Col4 nvarchar(10), Col5 varchar(10)
GO
SELECT is_ansi_padded, * FROM sys.columns WHERE object_id = object_id('ansi_test')
GO
SET ANSI_PADDING ON
GO
ALTER TABLE ansi_test ALTER COLUMN Col4 nvarchar(10)
ALTER TABLE ansi_test ALTER COLUMN Col5 varchar(10)
GO
SELECT is_ansi_padded, * FROM sys.columns WHERE object_id = object_id('ansi_test')
GO

    我还整理了一个快速脚本来生成更改命令（注意我将它们更改为与以前相同的数据类型和大小）。然而，我没有考虑任何类型的约束、索引或键会阻碍。您可能需要通过删除索引/约束/键等方式来逐一处理这些列，进行更改，然后重新创建所需的索引/约束/键等。我也没有没有时间对此进行大量测试，因此我建议一次运行一两个更改，并确保除ANSI_PADDING设置外一切都保持不变。

    SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(sys.objects.object_id) + '.' + 
            sys.objects.name + ' ALTER COLUMN ' + sys.columns.name + ' ' + 
            sys.types.name + CASE WHEN sys.types.name IN ('text','ntext') THEN ''
                            WHEN sys.types.name IN ('char','varchar') 
                                THEN '('+CAST(sys.columns.max_length AS varchar(10))+')'
                            WHEN sys.types.name IN ('nchar','nvarchar') 
                                THEN '('+CAST(sys.columns.max_length/2 AS varchar(10))+')' END +
            ' ' + CASE WHEN sys.columns.is_nullable = 0 
                        THEN 'NOT NULL' ELSE 'NULL' END
FROM sys.columns
JOIN sys.types
    ON sys.columns.user_type_id = sys.types.user_type_id
JOIN sys.objects
    ON sys.columns.object_id = sys.objects.object_id
WHERE is_ansi_padded = 0
  AND sys.types.name in ('char','varchar')
  AND sys.objects.type = 'U'
    • 5

相关问题