将新表列添加到特定序数位置

Question

是否可以在 SQL Server 中的特定序数位置向表中添加列？

例如，我们的表在每个表定义的“末尾”总是有 CreatedOn、CreatedBy、LastModifiedOn、LastModifiedBy 列？ 我希望新列显示在 SSMS 中这些列的上方。

如果我正在编写所有数据库更改的脚本，是否有办法在表末尾保留此顺序？

仅供参考，我并不是想就是否应该这样做展开一场激烈的战争。 如果您想阅读有关快速退化的线程，这里有一个很好的线程：

http://www.developersdex.com/sql/message.asp?p=581&r=5014513

Answer 1

您必须创建一个临时表来镜像原始表的架构，但具有所需的列顺序，然后将原始表的内容复制到临时表。 删除原来的并重命名temp。

这就是 SQL Management Studio 在幕后所做的事情。

使用架构同步工具，您可以自动生成这些脚本。

Answer 2

进入SQL Server Management Studio，并“设计”一个现有的表。 在中间插入一列，右键单击空白区域并选择生成更改脚本...

现在查看它创建的脚本。 它基本上会创建一个具有正确列顺序的临时表，从原始表中插入数据，删除原始表，然后重命名临时表。 这可能就是您需要做的。

您可能还需要取消选中此选项以允许创建更改脚本

Answer 3

答案是肯定的，技术上是可以的，但是这样做你会很头疼，而且执行和设置的时间会很长。

一：创建/复制/删除/重命名

这实际上是 SQL Server 在图形界面中所做的事情：下面是添加后单击“保存”按钮时它生成和执行的脚本的示例将新列添加到表的开头。

/* 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
CREATE TABLE dbo.Tmp_SomeTable
    (
    MyNewColumn int NOT NULL,
    OriginalIntColumn int NULL,
    OriginalVarcharColumn varchar(100) NULL
    )  ON [PRIMARY]
     TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_SomeTable SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_SomeTable ON
GO
IF EXISTS(SELECT * FROM dbo.SomeTable)
     EXEC('INSERT INTO dbo.Tmp_SomeTable (OriginalIntColumn, OriginalVarcharColumn FROM dbo.SomeTable WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_SomeTable OFF
GO
DROP TABLE dbo.SomeTable
GO
EXECUTE sp_rename N'dbo.Tmp_SomeTable', N'SomeTable', 'OBJECT' 
GO

GO
COMMIT

二：添加列/更新/删除列/重命名

此方法基本上涉及创建要添加到新列“右侧”的任何现有列的副本，将数据传输到新列列，然后删除原始列并重命名新列。 这会对您拥有的任何索引或约束造成严重破坏，因为您必须重新指向它们。 这在技术上是可行的，但在开发和执行方面同样耗时。

CREATE TABLE MyTest (a int, b int, d int, e int)

INSERT INTO MyTest (a,b,d,e) VALUES(1,2,4,5)

SELECT * FROM MyTest -- your current table

ALTER TABLE MyTest ADD c int -- add a new column
ALTER TABLE MyTest ADD d_new int -- create copies of the existing columns you want to move
ALTER TABLE MyTest ADD e_new int

UPDATE MyTest SET d_new = d, e_new = e -- transfer data to the new columns

ALTER TABLE MyTest DROP COLUMN d -- remove the originals
ALTER TABLE MyTest DROP COLUMN e

EXEC SP_RENAME 'MyTest.d_new', 'd'; -- rename the new columns
EXEC SP_RENAME 'MyTest.e_new', 'e';

SELECT * FROM MyTest 

DROP TABLE MyTest -- clean up the sample

三：接受它

这极大地侵犯了我的秩序感……但有时，它根本不值得重新洗牌。

Answer 4

据我所知，没有已知的方法可以更改列的顺序。 SQL Management Studio 在幕后执行 Jose Basilio 所说的操作。 如果您有一个大表，那么像这样更改列顺序是不切实际的。

您可以使用“视图”。 使用 SQL 视图，您可以使用任何您喜欢的顺序，而不会受到表列更改的影响。

Answer 5

我正在使用 SSMS 18。我以简单的方式

• 打开了表的设计
• ，通过拖动来定位所需的列
• ，并且根据 KM 的答案（线程中的第二个） - 取消选中允许创建更改脚本的选项，请参阅上面的图片。
• 保存更改。
• 完毕。 现在检查一下你的桌子。

Answer 6

我认为简单的是添加列 ALTER TABLE table1 ADD .. 然后从 select 创建一个像 tmp_table1 这样的 tmp 表
从 table1 中选择 col1,col2,col5,col3,col4 到 tmp_table1;
然后删除table1并将tmp_table1重命名为table1，就是这样。 我希望它能帮助别人

Answer 7

将所有列选择到临时表中，然后使用所需的新列创建一个新表。 然后删除旧表，从临时表中选择所有列，并将它们插入到具有重新排序的列的新表中。 没有数据丢失。

SELECT * FROM TEMP
SELECT * FROM originaltbl
SELECT * FROM #Stagintbl

DECLARE @ColumnName nvarchar(max);
SET @ColumnName=(SELECT
    DISTINCT STUFF((
        SELECT ',' + a.COLUMN_NAME
        FROM (
            SELECT Column_name 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME='originaltbl') a
        for xml path('')
    ),1,1,'') AS ColumnName)

DECLARE @Sqlquery nvarchar(max)
SET @Sqlquery = 'SELECT ' + @ColumnName + ' FROM #Stagintbl' + '';
INSERT INTO originaltbl
EXECUTE(@Sqlquery)

Answer 8

TFS 2013 会自动为您执行此操作。

按照您喜欢的方式将新列添加到表中，然后将更改提交到 TFS。 从那里，您可以在 Visual Studio 中打开表的 sql 文件，并手动移动 T-SQL CREATE 脚本中的列顺序。 然后，您可以使用 VS 的“工具”>“模式比较”工具来更新目标数据库。 SQL服务器> 新架构比较。 选择您的数据库项目，将您的更改作为源，并将要更新的数据库作为目标。 比较，选择表的脚本，然后更新。 VS会自动删除和添加。 您的所有数据和索引都将是安全的。

Answer 9

肮脏而简单。
将表导出为 csv。
在所需位置插入新数据。
删除表。
使用所需的列规格创建新表。
将 csv 中的列加载到新表中。

Answer 10

我不确定该线程是否仍处于活动状态。 我对 MySQL 数据库进行了相同的查询。 右键单击表并选择“ALTER”自动生成以下代码。 sakila db 提供的示例并且有效。 只需找出要在其后放置新列的列并使用“AFTER”关键字

ALTER TABLE `sakila`.`actor` 
CHANGE COLUMN `middle_name` `middle_name` VARCHAR(50) NULL DEFAULT NULL AFTER `first_name`;