sql-server - registros - ordenar columnas de una tabla en sql server

Agregue una nueva columna de tabla a una posición ordinal específica en Microsoft SQL Server

Question

¿Es posible agregar una columna a una tabla en una posición ordinal específica en Microsoft SQL Server?

Por ejemplo, nuestras tablas siempre tienen las columnas CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy en el "final" de cada definición de tabla. Me gustaría que la nueva columna se muestre en SSMS sobre estas columnas.

Si estoy ejecutando todos los cambios en mi base de datos, ¿hay alguna forma de conservar este orden al final de la tabla?

Para su información, no estoy tratando de instituir una guerra de fuego en caso de que esto debería hacerse. Si quieres leer sobre un hilo que degenera rápidamente en eso, aquí hay uno bueno:

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

Answer 1

Debe crear una tabla temporal que refleje el esquema de la tabla original pero con el orden de columna que desee, luego copiar el contenido del original en la temperatura. Eliminar el original y cambiar el nombre de la temperatura.

Esto es lo que hace SQL Management Studio detrás de escena.

Con una herramienta de sincronización de esquemas, puede generar estos scripts automáticamente.

Answer 2

La respuesta es sí, es técnicamente posible, pero tendrá un dolor de cabeza al hacerlo y le llevará mucho tiempo ejecutarlo y configurarlo.

Uno: Crear / Copiar / Eliminar / Renombrar

Esto es realmente lo que hace SQL Server en la interfaz gráfica: aquí hay un ejemplo del script que genera y ejecuta al hacer clic en el botón ''guardar'' después de agregar una nueva columna al comienzo de una tabla.

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

Dos: AÑADIR COLUMNA / ACTUALIZACIÓN / DESACTIVAR COLUMNA / RENOMBRAR

Básicamente, este método consiste en crear una copia de cualquier columna existente que desee agregar al ''derecho'' de su nueva columna, transferir los datos a la nueva columna, luego quitar los originales y cambiar el nombre de los nuevos. Esto causará estragos en cualquier índice o restricción que tenga, ya que tiene que ubicarlos. Es técnicamente posible, pero nuevamente requiere mucho tiempo tanto en términos de desarrollo como de ejecución.

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

Tres: vivir con eso

Esto ofende gravemente mi sentido del orden ... pero a veces, no vale la pena reorganizarlo.

Answer 3

Lo que creo que es simple es agregar la columna ALTER TABLE table1 ADD ... y luego crear una tabla tmp como tmp_table1 desde la selección como SELECT col1,col2,col5,col3,col4 into tmp_table1 from table1; y luego suelte table1 y cambie el nombre de tmp_table1 a table1, eso es todo. Espero que ayude a alguien

Answer 4

No estoy seguro de si el hilo todavía está activo. Estaba teniendo la misma consulta con la base de datos MySQL. Al hacer clic derecho en la tabla y seleccionar "ALTER", se genera automáticamente el siguiente código. Muestra provista desde sakila db y funcionó. Simplemente busque la columna después de la cual desea colocar su nueva columna y use la palabra clave ''DESPUÉS''

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

Answer 5

No puede crear una columna en una ubicación ordinal específica en la estructura de la tabla. Además, ¿por qué querría? El orden de las columnas en SQL Server es totalmente irrelevante.

SQL Server Management Studio tiene una vista de diseño que aparentemente le permite crear una columna en cualquier ubicación de la lista de columnas, pero lo que realmente sucede aquí en segundo plano es que la nueva tabla con las nuevas columnas se crea y la anterior se elimina. .

No hay comandos SQL DDL para crear una columna en una ubicación específica o para "reordenar" las columnas. Realmente no es necesario.

Bagazo

Answer 6

Que yo sepa, no hay ningún método conocido para cambiar el orden de la columna. Detrás de escena, SQL Management Studio hace lo que dijo José Basilio. Y si tiene una tabla grande, entonces no es práctico cambiar los pedidos de columna de esta manera.

Puede utilizar una "vista". Con las vistas SQL puede usar cualquier orden que desee sin verse afectado por los cambios en la columna de la tabla.

Answer 7

Sucio y sencillo.
Exportar tabla a csv.
Insertar nuevos datos en la posición deseada.
Mesa plegable.
Crear nueva tabla con las especificaciones de columna deseadas.
Cargar columnas desde csv a la nueva tabla.

Answer 8

Vaya a SQL Server Management Studio y "diseñe" una tabla existente. Inserte una columna en el centro, haga clic derecho en un área vacía y seleccione Generar cambio de script ...

Ahora mira el script que crea. básicamente creará una tabla temporal con el orden de columna adecuado, insertará los datos de la tabla original, eliminará la tabla original y cambiará el nombre de la tabla temporal. Esto es probablemente lo que tendrás que hacer.

Es posible que también deba desmarcar esta opción para permitir la creación de scripts de cambio.

Answer 9

TFS 2013 lo hará por usted automáticamente .

Agregue la (s) nueva (s) columna (s) a la tabla de la forma que desee, y luego confirme los cambios en TFS. Desde allí puede abrir el archivo sql de la tabla en Visual Studio y mover manualmente el orden de las columnas en el script CREATE de T-SQL. Luego, puede actualizar su base de datos de destino utilizando la herramienta de comparación de esquemas de VS que se encuentra en Herramientas> SQL Server> Nueva comparación de esquemas. Elija su proyecto de base de datos con su cambio como fuente y la base de datos que desea actualizar como destino. Compare, seleccione la secuencia de comandos de la tabla y Actualizar. VS se caerá y agregará automáticamente. Todos sus datos estarán seguros, y los índices también.