如何在SQL Server中一次性修改多个列

Question

我需要改变表中多个列的数据类型，对于单个列，以下代码可以正常使用：

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0) 

但是我如何在一条语句中更改多个列？以下方法无效：

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0), 
    CM_CommodityID NUMERIC(18,0)

- D.mahesh

---

一次性完成的好处是什么？ - onedaywhen

为了让 SQL Server 只需一次通过该表以执行任何必要的新数据类型验证和/或用新格式编写更改后的列。 - Martin Smith

相反，对于大型表上的多列，如果可以在2小时而不是24小时内运行一次备用运算，将是一个巨大的优势。 - Norbert Kardos

感知的好处：当修改脚本以在不同的数据库中运行相同的表时，可以减少复制和粘贴维护。 - RoboticRenaissance

Answer 1

这是不可能的。您需要逐个操作。

您可以:

1. 创建一个包含修改列的临时表
2. 将数据复制到临时表中
3. 删除原始表(在进行任何更改前务必仔细检查!)
4. 将临时表重命名为原始表名称

- Neil Knight

---

你需要逐个执行。所以，问题不大，只需使用多个“ALTER TABLE ALTER COLUMN”命令即可。 - KM.

一个问题是如果你要修改一个大表格。每个语句都需要进行新的扫描，但是如果你可以同时修改多列，所有的修改可能会更快。 - erikkallen

@erikkallen，然后像SSMS工具通常生成脚本一样操作：创建新表并复制FK和索引等信息，删除原始表，然后重命名新表。 - KM.

删除和重新创建表是一项相当繁重的操作。现在在SSMS中默认禁用了它，这很可能是有充分理由的。 - jocull

Answer 2

在单个ALTER TABLE语句中执行多个ALTER COLUMN操作是不可行的。

请参阅ALTER TABLE语法此处

您可以执行多个ADD或多个DROP COLUMN，但只能执行一个ALTER COLUMN。

- Fernando Torres

Answer 3

就像其他人已经回答的那样，您需要多个ALTER TABLE语句。
尝试以下操作：

ALTER TABLE tblcommodityOHLC alter column CC_CommodityContractID NUMERIC(18,0);
ALTER TABLE tblcommodityOHLC alter column CM_CommodityID NUMERIC(18,0);

- Ray K.

---

使用SSMS或甚至Notepad ++下的复制和粘贴+窗口函数，您可以列出列和新类型或约束，并在需要修改的每个列之前添加ALTER TABLE x ALTER COLUMN。看起来有点俗气，但首先拥有这样一个不协调的表规范也是如此。 - alejandrob

Answer 4

以下解决方案不是一个用于修改多个列的单个语句，但确实使生活更简单：

1. 生成表的 CREATE 脚本。

2. 将第一行的 CREATE TABLE 替换为 ALTER TABLE [TableName] ALTER COLUMN。

3. 从列表中删除不需要的列。

4. 按您想要的方式更改列的数据类型。

5. 执行以下“查找和替换”：

   1. 查找：NULL,
   2. 替换为：NULL; ALTER TABLE [TableName] ALTER COLUMN
   3. 点击“替换”按钮。

6. 运行脚本。

希望这可以节省很多时间 :))

- Pritam

Answer 5

就像其他人所说的那样，您需要使用多个ALTER COLUMN语句，每个语句对应一个要修改的列。

如果您想将表中所有或多个列修改为相同的数据类型（例如将VARCHAR字段从50个字符扩展到100个字符），可以使用以下查询自动生成所有语句。如果您想在多个字段中替换相同的字符（例如从所有列中删除 \t ），此技术也很有用。

SELECT
     TABLE_CATALOG
    ,TABLE_SCHEMA
    ,TABLE_NAME
    ,COLUMN_NAME
    ,'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] VARCHAR(300)' as 'code'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table' AND TABLE_SCHEMA = 'your_schema'

这会为每个列生成一个ALTER TABLE语句。

- Evan

Answer 6

如果您不想亲自编写整个内容并将所有列更改为相同的数据类型，可以使用以下方法简化操作：
如果你不想自己写整个东西，并改变所有的列到同一数据类型，这会让它更容易：

select 'alter table tblcommodityOHLC alter column '+name+ 'NUMERIC(18,0);'
from syscolumns where id = object_id('tblcommodityOHLC ')

最初的回答

您可以将输出复制并粘贴为查询

- Rishi

Answer 7

如果您在管理工具中进行更改并生成脚本，则会创建一个新表，并将旧数据插入其中，同时更改了数据类型。以下是一个小例子，更改了两列的数据类型。

/*
   12 August 201008:30:39
   User: 
   Server: CLPPRGRTEL01\TELSQLEXPRESS
   Database: Tracker_3
   Application: 
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.tblDiary
    DROP CONSTRAINT FK_tblDiary_tblDiary_events
GO
ALTER TABLE dbo.tblDiary_events SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_tblDiary
    (
    Diary_ID int NOT NULL IDENTITY (1, 1),
    Date date NOT NULL,
    Diary_event_type_ID int NOT NULL,
    Notes varchar(MAX) NULL,
    Expected_call_volumes real NULL,
    Expected_duration real NULL,
    Skill_affected smallint NULL
    )  ON T3_Data_2
     TEXTIMAGE_ON T3_Data_2
GO
ALTER TABLE dbo.Tmp_tblDiary SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_tblDiary ON
GO
IF EXISTS(SELECT * FROM dbo.tblDiary)
     EXEC('INSERT INTO dbo.Tmp_tblDiary (Diary_ID, Date, Diary_event_type_ID, Notes, Expected_call_volumes, Expected_duration, Skill_affected)
        SELECT Diary_ID, Date, Diary_event_type_ID, CONVERT(varchar(MAX), Notes), Expected_call_volumes, Expected_duration, CONVERT(smallint, Skill_affected) FROM dbo.tblDiary WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_tblDiary OFF
GO
DROP TABLE dbo.tblDiary
GO
EXECUTE sp_rename N'dbo.Tmp_tblDiary', N'tblDiary', 'OBJECT' 
GO
ALTER TABLE dbo.tblDiary ADD CONSTRAINT
    PK_tblDiary PRIMARY KEY NONCLUSTERED 
    (
    Diary_ID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2

GO
CREATE UNIQUE CLUSTERED INDEX tblDiary_ID ON dbo.tblDiary
    (
    Diary_ID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
GO
CREATE NONCLUSTERED INDEX tblDiary_date ON dbo.tblDiary
    (
    Date
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
GO
ALTER TABLE dbo.tblDiary WITH NOCHECK ADD CONSTRAINT
    FK_tblDiary_tblDiary_events FOREIGN KEY
    (
    Diary_event_type_ID
    ) REFERENCES dbo.tblDiary_events
    (
    Diary_event_ID
    ) ON UPDATE  CASCADE 
     ON DELETE  CASCADE 

GO
COMMIT

- Kevin Ross

Answer 8

-- create temp table 
CREATE TABLE temp_table_alter
(
column_name varchar(255)    
);

-- insert those coulmns in temp table for which we nee to alter size of columns 
INSERT INTO temp_table_alter (column_name) VALUES ('colm1');
INSERT INTO temp_table_alter (column_name) VALUES ('colm2');
INSERT INTO temp_table_alter (column_name) VALUES ('colm3');
INSERT INTO temp_table_alter (column_name) VALUES ('colm4');

DECLARE @col_name_var varchar(255);
DECLARE alter_table_cursor CURSOR FOR
select column_name from temp_table_alter ;

OPEN alter_table_cursor
FETCH NEXT FROM alter_table_cursor INTO @col_name_var
WHILE @@FETCH_STATUS = 0
 BEGIN

 PRINT('ALTER COLUMN ' + @col_name_var);
 EXEC ('ALTER TABLE Original-table  ALTER COLUMN ['+ @col_name_var + '] DECIMAL(11,2);')

 FETCH NEXT FROM alter_table_cursor INTO @col_name_var
 END

CLOSE alter_table_cursor
DEALLOCATE alter_table_cursor

-- at the end drop temp table
drop table temp_table_alter;

- Aurangzeb khan

---

不是一个好的解决方案。游标和循环应该尽可能避免使用！！！ - Ray K.

不是这样的，Ray。对于某些DDL任务和其他必要的RBR任务，游标和循环是可以使用的。 - Jeff Moden

Answer 9

感谢Evan的代码示例，我能够对其进行修改，使其更加针对以某些特定列名开头的表，并且还可以处理约束的具体细节。我运行了该代码，然后复制了[COLUMN]列并成功执行了它。

USE [Table_Name]
GO
SELECT
     TABLE_CATALOG
    ,TABLE_SCHEMA
    ,TABLE_NAME
    ,COLUMN_NAME
    ,DATA_TYPE
    ,'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] DROP CONSTRAINT [DEFAULT_'+TABLE_NAME+'_'+COLUMN_NAME+']; 
ALTER TABLE  ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] datetime2 (7) NOT NULL 
ALTER TABLE  ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ADD CONSTRAINT [DEFAULT_'+TABLE_NAME+'_'+COLUMN_NAME+'] DEFAULT (''3/6/2018 6:47:23 PM'') FOR ['+COLUMN_NAME+']; 
GO' AS '[CODE]'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'form_%' AND TABLE_SCHEMA = 'dbo'
 AND (COLUMN_NAME = 'FormInserted' OR COLUMN_NAME = 'FormUpdated')
 AND DATA_TYPE = 'datetime'

- Brenden Kehren

Answer 10

select 'ALTER TABLE ' + OBJECT_NAME(o.object_id) + 
    ' ALTER COLUMN ' + c.name + ' DATETIME2 ' + 
    CASE WHEN c.is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
from sys.objects o
inner join sys.columns c on o.object_id = c.object_id
inner join sys.types t on c.system_type_id = t.system_type_id
where o.type='U'
and c.name = 'Timestamp'
and t.name = 'datetime'
order by OBJECT_NAME(o.object_id)

由devio提供的免费服务。

- Spyder