将数据库架构中的所有"SMALLINT"列转换为"BIT"的最佳方法？(SQL)

Question

如何将数据库中的所有smallint类型列转换为bit类型？我正在使用 SQL Server 2008。

Answer 1

在SQL Server中，您可以使用ALTER TABLE my_table ALTER COLUMN my_column [new_datatype] .

小心默认值之类的东西，因为我还没有用它们进行测试。

示例 1 - 将给出查询列表供您查看/修改/执行（更安全的选项）。

DECLARE @TableSchema VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @Query NVARCHAR(1000)
DECLARE @FromDataType NVARCHAR(50)
DECLARE @ToDataType NVARCHAR(50)
SET @TableSchema = 'dbo';
SET @FromDataType = 'smallint';
SET @ToDataType = 'bit';
DECLARE c CURSOR FAST_FORWARD FOR 
    SELECT TABLE_NAME, COLUMN_NAME 
    FROM   INFORMATION_SCHEMA.COLUMNS 
    WHERE  TABLE_SCHEMA = @TableSchema 
       AND TABLE_NAME <> 'sysdiagrams' 
       AND DATA_TYPE = @FromDataType
OPEN c;
FETCH NEXT FROM c INTO @TableName, @ColumnName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Query = N'ALTER TABLE ' + @TableName + N' ALTER COLUMN ' + @ColumnName + N' ' + @ToDataType -- + CHAR(13) + N'GO'
    PRINT @Query
    EXEC sp_EXECUTESQL @Query
    FETCH NEXT FROM c INTO @TableName, @ColumnName;
END
CLOSE c;
DEALLOCATE c;

示例 2 - 将执行（建议先运行示例 1！

DECLARE @TableSchema VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @Query NVARCHAR(1000)
DECLARE @FromDataType NVARCHAR(50)
DECLARE @ToDataType NVARCHAR(50)
SET @TableSchema = 'dbo';
SET @FromDataType = 'smallint';
SET @ToDataType = 'bit';
DECLARE c CURSOR FAST_FORWARD FOR 
    SELECT TABLE_NAME, COLUMN_NAME 
    FROM   INFORMATION_SCHEMA.COLUMNS 
    WHERE  TABLE_SCHEMA = @TableSchema 
       AND TABLE_NAME <> 'sysdiagrams' 
       AND DATA_TYPE = @FromDataType
OPEN c;
FETCH NEXT FROM c INTO @TableName, @ColumnName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Query = N'ALTER TABLE ' + @TableName + N' ALTER COLUMN ' + @ColumnName + N' ' + @ToDataType + CHAR(13) + N' GO'
    PRINT @Query
    FETCH NEXT FROM c INTO @TableName, @ColumnName;
END
CLOSE c;
DEALLOCATE c;

Answer 2

请小心执行此操作，并首先在备份数据库上进行测试。 以下查询将为数据库中具有 SMALLINT 数据类型的每一列创建 ALTER 语句，以将它们转换为 BIT。

select 'ALTER TABLE ' + QUOTENAME(o.Name) + ' ALTER COLUMN ' + QUOTENAME(c.Name) + ' BIT' as Command
from sys.objects o
inner join sys.columns c
on o.object_id = c.object_id
where system_type_id = 52
and o.Type = 'U'

此外，请确保列出的每列仅包含 1 或 0，否则在运行脚本时会出现截断错误。

Answer 3

如果您询问有关转换列数据类型的问题，我认为您不能直接这样做。 您可以将新列添加为 BIT 并从旧列填充它，然后删除旧列并将新列重命名回旧名称。

有关详细信息，请参阅联机文档。