logo标签列表

将所有 SQL Server 列从 BigInt 类型更改为 Int 类型。

sql-serversql-server-2008-r2
4
我需要处理一个继承而来的数据库。所有数字字段都被设置为bigint类型（尽管它们的值低于5000），但没有任何理由。
我该如何以程序化的方式将所有bigint类型的列更改为int类型？这是可能的吗？会不会引起任何现有的约束等问题？
我只想更改表格和特定数据库中的内容。
我正在使用SQL Server 2008 R2。
我需要对数百个字段执行此操作，希望一次运行即可更新所有表格字段。我想保留任何现有的约束、空值状态和默认值。
因此，基本上是将整个数据库中的bigint类型更改为int类型，除了字段类型之外不更改任何其他内容。
谢谢！
如果它们都小于5k，为什么不使用smallint？相比于bigint的8个字节，它只有2个字节，并且可以具有超过32k的值。 - JNK
系统正在扩展，因此他们有可能会增长到那个规模。 - GraemeMiller
1
如果您想自动为每个表执行此操作，您可能还需要查看未记录的存储过程sp_foreachtable。 - gjvdkamp
290Mb。我可以愉快地将数据库下线以执行任何操作。 - GraemeMiller
5个回答
7

我曾经遇到过这种问题。我需要将int更改为bigint。这很困难，但是可以实现。使用以下语句更改数据类型非常容易：

Alter table myTable alter column targetcolumn bigint not null

但是，如果您的列涉及约束关系，则必须先删除约束，然后进行更改，最后重新创建约束。

Alter table myTable drop constraint [fkconstraintname]
Alter table myTable alter column targetcolumn int not null
Alter table othertable alter column targetcolumn int not null
Alter table myTable add constraint [fkconstraintname] foreign key (targetcolumn) references othertable(targetcolumn)

编辑

如果有很多约束条件，那么更改它会非常麻烦。如果有很多带有约束条件的表格，并且没有极端的紧急情况需要更改，请不要这样做。

编辑

然后你可以这样做。通过管理工具连接到Sql Server，右键单击必要的数据库 => 任务 => 生成脚本。

下一步 => 下一步

Necessary window

在此时按高级选项。将要脚本化的数据类型设置为模式和数据。选择任何适合您的输出（文件、查询窗口）？单击“确定”并继续执行。它将为您生成完整的DDL和DML，如下所示：

USE [master]
GO
/****** Object:  Database [Zafarga]    Script Date: 02/02/2012 19:31:55 ******/
CREATE DATABASE [Zafarga] ON  PRIMARY 

GO
ALTER DATABASE [Zafarga] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Zafarga].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Zafarga] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Zafarga] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Zafarga] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Zafarga] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Zafarga] SET ARITHABORT OFF
GO
ALTER DATABASE [Zafarga] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Zafarga] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Zafarga] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Zafarga] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Zafarga] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Zafarga] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [Zafarga] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Zafarga] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Zafarga] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Zafarga] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Zafarga] SET  ENABLE_BROKER
GO
ALTER DATABASE [Zafarga] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Zafarga] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Zafarga] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Zafarga] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Zafarga] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Zafarga] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Zafarga] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Zafarga] SET  READ_WRITE
GO
ALTER DATABASE [Zafarga] SET RECOVERY FULL
GO
ALTER DATABASE [Zafarga] SET  MULTI_USER
GO
ALTER DATABASE [Zafarga] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Zafarga] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'Zafarga', N'ON'
GO
USE [Zafarga]
GO
/****** Object:  Table [dbo].[Category]    Script Date: 02/02/2012 19:31:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
    [CategoryId] [bigint] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
    [CategoryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Product]    Script Date: 02/02/2012 19:31:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
    [ProductId] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Price] [decimal](18, 2) NOT NULL,
    [CategoryId] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [ProductId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  ForeignKey [Category_Products]    Script Date: 02/02/2012 19:31:56 ******/
ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [Category_Products] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([CategoryId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [Category_Products]
GO

适当更改所有数据类型，然后运行。

您说所有数据都不到5000行。因此，无需修改插入语句。

准备好需要花费很长时间。

编辑

这将生成一个新的数据库，因此请准备好重命名您的原始数据库或新创建的数据库。

.Net似乎不喜欢不同可空整数类型之间的转换。如果系统中只有int而不是int和bigint混合，则可以使代码更容易。 - GraemeMiller
2
我认为 @Oybek 在他的附录中提到了这一点，我给他加上+1，但为了确保，我将概述如何执行此操作。（假设您有许多约束条件跨越许多 bigint 表；如果没有，逐个执行可能更简单。）

  • 使用 SSMS 脚本整个数据库。 （在对象资源管理器窗格中右键单击数据库，选择“脚本数据库”，选择“创建到”，然后将其添加到新查询窗口并保存为文件。

  • 搜索和替换所有 bigints 为 ints。（慢慢来，确保只更改必须更改的内容。）

  • 修改脚本以创建新数据库（不同名称，不同文件）

  • 运行并创建数据库。

  • 难点：将旧数据库的表中的数据复制到新数据库。

如果像您说的那样，所有 bigints 都适合 ints，那么您不应该遇到任何问题；难点是找出实际执行所有操作的方法。肯定可以通过 SSMS 导入或导出向导来完成此操作，但我对这些工具并不太熟悉。如果没有，创建一系列 INSERT… SELECT… 语句按父级到子级表的顺序执行，并根据需要设置 IDENTITY INSERT，应该可以解决问题。

在生成脚本窗口中将选项“要生成的数据类型”设置为“模式和数据”，将创建带有各种“标识插入”选项的所有插入语句。你的回答很棒，简短明了)) - Oybek
0

关于Martin Smith的问题 - 它比我想象的要简单得多：

ALTER TABLE YourTable MODIFY COLUMN OldColumn INT [NOT] NULL

就是这样

回答的旧版本：

如果新列不应该为空：

ALTER TABLE YourTable ADD NewFIeld INT NOT NULL DEFAULT (CAST(OldField AS INT))
GO
ALTER TABLE YourTable DROP COLUMN OldField
GO
exec sp_rename 'YourTable.NewField', 'OldField', 'COLUMN'

OR（或者）基本上是相同的，对于可空列更加宽松 - 在执行更新操作时不会锁定系统表

ALTER TABLE YourTable ADD NewFIeld INT NULL
GO
UPDATE YourTable SET NewField=OldField
GO
ALTER TABLE YourTable DROP COLUMN OldField
GO
exec sp_rename 'YourTable.NewField', 'OldField', 'COLUMN'
添加新列的意义何在？ALTER TABLE ... ALTER COLUMN虽然可以正常工作，但两者仍会浪费空间，直到重建。 - Martin Smith
我想在许多字段和表格中执行此操作。可能超过200个字段。有更自动化的方法吗？ - GraemeMiller
@MartinSmith没有说明该列是否可为空。 - Oleg Dok
@OlegDok - 你可以在 ALTER TABLE ... ALTER COLUMN 中指定。你的代码缺少任何锁来确保其他进程不会更新 OldField。 - Martin Smith
@MartinSmith 没错，我明白了，非常感谢你的建议，它们总是很有价值。 - Oleg Dok
0

我会这样做：

  1. 将YourTable重命名为YourTable_OLD
  2. 右键单击表格 -> 脚本表格作为创建 -> 到新查询窗口
  3. 调整脚本以使用int并将表格重命名为原始名称
  4. 创建新表并将其命名为YourTable以替换旧表
  5. 插入YourTable select * from YourTable_OLD
  6. 删除YourTable_OLD（当您确定一切正常时）

我想这可能是最快的方法。如果逐列进行转换，则需要逐列进行转换。这涉及大量IO，可能会成为问题，具体取决于您的表的大小。

敬礼 GJ

0

除了答案之外，不要忘记检查所有列上的值是否在整数范围内（介于-2,147,483,648和2,147,483,647之间）。

为了自动化@Oleg Dok的答案，您可以在脚本中运行他的解决方案，该脚本循环遍历您的DB上的所有表和所有列。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接