时间戳

首页 > 解决方案 > 如何在 SQL Server Management Studio 中移动列？

sql-server - 如何在 SQL Server Management Studio 中移动列？

问题描述

我最初创建表时忘记添加 IMPORT_DETAILLANDNBR 列。我必须将此列放在表中的 IMPORT_LANDNBR 列旁边，但系统会自动将 IMPORT_DETAILLANDNBR 列放在表的末尾。我怎样才能解决这个问题？这是我尝试过的：右键单击表格并单击“设计”选项卡，但是当我在“设计”中移动列时，它会移动，但不会停留。它通过移回表的末尾来恢复。

标签： sql-serverdatabase

解决方案

如果您确实需要更改表的列顺序，SSMS 表设计器可以执行此操作。当您保存更改时，SSMS 将生成一个更改脚本，该脚本会创建一个新表、复制所有数据、删除旧表并重命名新表。例如：

/* 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_TEST_TABLE
    (
    COLLECT_DATA_SEQ int NOT NULL,
    COLUMN2 varchar(36) NOT NULL,
    COLUMN3 varchar(20) NOT NULL,
    COLUMN4 varchar(20) NOT NULL,
    COLUMN5 tinyint NULL,
    COLUMN6 datetime NOT NULL,
    COLUMN7 datetime NULL,
    COLUMN15 datetime NULL,
    COLUMN8 varchar(10) NOT NULL,
    COLUMN9 tinyint NOT NULL,
    COLUMN10 tinyint NOT NULL,
    COLUMN11 tinyint NULL,
    COLUMN12 tinyint NOT NULL,
    COLUMN13 varchar(36) NOT NULL,
    COLUMN14 tinyint NOT NULL,
    COLUMN16 char(8) NULL,
    COLUMN18 char(8) NULL,
    COLUMN17 datetime NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TEST_TABLE SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.TEST_TABLE)
     EXEC('INSERT INTO dbo.Tmp_TEST_TABLE (COLLECT_DATA_SEQ, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6, COLUMN7, COLUMN15, COLUMN8, COLUMN9, COLUMN10, COLUMN11, COLUMN12, COLUMN13, COLUMN14, COLUMN16, COLUMN18, COLUMN17)
        SELECT COLLECT_DATA_SEQ, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6, COLUMN7, COLUMN15, COLUMN8, COLUMN9, COLUMN10, COLUMN11, COLUMN12, COLUMN13, COLUMN14, COLUMN16, COLUMN18, COLUMN17 FROM dbo.TEST_TABLE WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TEST_TABLE
GO
EXECUTE sp_rename N'dbo.Tmp_TEST_TABLE', N'TEST_TABLE', 'OBJECT' 
GO
ALTER TABLE dbo.TEST_TABLE ADD CONSTRAINT
    PK_TEST_TABLE PRIMARY KEY CLUSTERED 
    (
    COLLECT_DATA_SEQ
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

推荐阅读