我需要更改表中几个列的数据类型。
对于单个列，下面的工作很好:
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)
当前回答
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提供
其他回答
如果我正确理解了你的问题，你可以使用下面提到的查询在一个表中添加多个列。
查询:
Alter table tablename add (column1 dataype, column2 datatype);
多亏了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'
-- 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;
正如其他人回答的那样，您需要多个ALTER TABLE语句。 试一试:
ALTER TABLE tblcommodityOHLC alter column CC_CommodityContractID NUMERIC(18,0);
ALTER TABLE tblcommodityOHLC alter column CM_CommodityID NUMERIC(18,0);
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提供
