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

I need to ALTER the data types of several columns in a table.

我需要更改表中几个列的数据类型。

For a single column, the following works fine:

对于单个列，以下工作良好:

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

But how do I alter multiple columns in one statement? The following does not work:

但是如何在一个语句中改变多个列呢?以下方法无效:

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

12 个解决方案

#1

This is not possible. You will need to do this one by one.

这是不可能的。你需要一个一个地做这个。

You could create a Temporary Table with your modified columns in, copy the data across, drop your original table and rename your Temporary Table to your original name.

您可以使用已修改的列创建一个临时表，复制数据，删除原始表，并将临时表重命名为原始名称。

#2

Doing multiple ALTER COLUMN actions inside a single ALTER TABLE statement is not possible.

在一个ALTER TABLE语句中执行多个ALTER列操作是不可能的。

See the ALTER TABLE syntax here:
http://msdn.microsoft.com/en-US/library/ms190273.aspx

请参阅这里的ALTER TABLE语法:http://msdn.microsoft.com/en-US/library/ms190273.aspx

You can do multiple ADD or multiple DROP COLUMN, but just one ALTER COLUMN.

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

#3

The following solution is not a single statement for altering multiple columns, but yes, it makes life simple:

下面的解决方案并不是改变多个列的单一语句，但是是的，它使生活变得简单:

1. Generate a table's CREATE script.

   生成表的创建脚本。

2. Replace CREATE TABLE with ALTER TABLE [TableName] ALTER COLUMN for first line

   将CREATE TABLE替换为第一行的ALTER TABLE [TableName] ALTER列。

3. Remove unwanted columns from list.

   从列表中删除不想要的列。

4. Change the columns data types as you want.

   根据需要更改列数据类型。

5. Perform a Find and Replace… as follows:

   执行查找和替换…如下:

   1. Find: NULL,
   2. 发现:空,
   3. Replace with: NULL; ALTER TABLE [TableName] ALTER COLUMN
   4. 替换为:空;改变表[TableName]改变列
   5. Hit Replace button.
   6. 点击更换按钮。

6. Run the script.

   运行脚本。

Hope it will save lot of time :))

希望它能节省很多时间

#4

As others have answered, you need multiple ALTER TABLE statements. Try:

正如其他人回答的那样，您需要多个ALTER TABLE语句。试一试:

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

etc.

等。

#5

As lots of others have said, you will need to use multiple ALTER COLUMN statements, one for each column you want to modify.

正如许多其他人所说的那样，您需要使用多个ALTER列语句，一个用于您想要修改的列。

If you want to modify all or several of the columns in your table to the same datatype (such as expanding a VARCHAR field from 50 to 100 chars), you can generate all the statements automatically using the query below. This technique is also useful if you want to replace the same character in multiple fields (such as removing \t from all columns).

如果您想将表中的所有或多个列修改为相同的数据类型(例如将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'

This generates an ALTER TABLE statement for each column for you.

这将为每一列生成一条ALTER TABLE语句。

#6

If you do the changes in management studio and generate scripts it makes a new table and inserts the old data into that with the changed data types. Here is a small example changing two column’s data types

如果您在management studio中执行更改并生成脚本，那么它将创建一个新表，并将旧数据插入已更改的数据类型中。下面是一个更改两列数据类型的小示例

/*
   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

#7

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)

courtesy of devio

由devio

#8

We can alter multiple columns in a single query like this:

我们可以在一个查询中修改多个列，如下所示:

ALTER TABLE `tblcommodityOHLC`
    CHANGE COLUMN `updated_on` `updated_on` DATETIME NULL DEFAULT NULL AFTER `updated_by`,
    CHANGE COLUMN `delivery_datetime` `delivery_datetime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP AFTER `delivery_status`;

Just give the queries as comma separated.

只需将查询作为逗号分隔。

#9

Thanks to Evan's code sample, I was able to modify it more and get it more specific to tables starting with, specific column names AND handle specifics for constraints too. I ran that code and then copied the [CODE] column and executed it without issue.

由于Evan的代码示例，我能够对它进行更多的修改，并使它更具体地针对表、特定的列名和约束的具体处理。我运行了这段代码，然后复制了[code]列，并无问题地执行了它。

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'

#10

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

——创建临时表创建表temp_table_alter (column_name varchar(255));

-- insert those coulmns in temp table for which we nee to alter size of columns

——在我们需要改变列大小的temp表中插入这些库仑

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

释放alter_table_cursor

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

——结束时的drop temp表drop table temp_table_alter;

#11

If i understood your question correctly you can add multiple columns in a table by using below mentioned query.

如果我理解正确，您可以使用下面提到的查询在表中添加多个列。

Query:

查询:

Alter table tablename add (column1 dataype, column2 datatype);

#12

Put ALTER COLUMN statement inside a bracket, it should work.

将ALTER列语句放在括号内，它应该可以工作。

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