ejemplo - modificar identity sql server
Añadiendo una identidad a una columna existente (19)
Necesito cambiar la clave principal de una tabla a una columna de identidad, y ya hay varias filas en la tabla.
Tengo una secuencia de comandos para limpiar las ID para garantizar que sean secuenciales a partir de 1, funciona bien en mi base de datos de prueba.
¿Cuál es el comando SQL para alterar la columna para tener una propiedad de identidad?
Básicamente hay cuatro pasos lógicos.
Crear una nueva columna de identidad. Active Insert Identity para esta nueva columna.
Inserte los datos de la columna de origen (la columna que desea convertir a Identidad) en esta nueva columna.
Desactive la Inserción de Identidad para la nueva columna.
Suelte la columna de origen y cambie el nombre de la columna nueva al nombre de la columna de origen.
Puede haber algunas complejidades más, como trabajar en varios servidores, etc.
Consulte el siguiente artículo para los pasos (usando ssms y T-sql). Estos pasos están diseñados para principiantes con menos control sobre T-SQL.
Como entendí en casos normales, estamos creando una tabla con clave principal que tiene propiedad de identidad
Por lo tanto, no es posible cambiar el nombre o eliminar una columna que está asociada con la restricción de clave principal porque las reglas de restricción están validando la estructura de la columna.
Para lograr esto tenemos que procesar algunos pasos de la siguiente manera:
Asumamos TableName = ''Employee'' y ColumnName = ''EmployeeId''
1. Agregue la nueva columna ''EmployeeId_new'' en la tabla ''Employee''
ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY (1,1)
Ahora elimine la columna ''EmployeeId'' de la tabla ''Employee''
ALTER TABLE Employee DROP COLUMN EmployeeIdEsto generará un error debido a que las reglas de restricción de clave primaria son aplicables y validan la estructura de la columna.
* ### '' Msg 5074, Nivel 16, Estado 1, Línea 1 El objeto [PK_dbo.Employee] depende de colmn [EmployeeId].'' ###Por lo tanto, primero tenemos que eliminar la restricción de la Clave principal de la tabla ''Empleado'' y luego podemos eliminar la columna
ALTER TABLE Restricción DROP de empleado [PK_dbo.Employee]Ahora podemos eliminar la columna ''EmployeeId'' de la tabla ''Employee'' como lo hicimos en el paso anterior donde obtuvimos el error
ALTER TABLE Employee DROP COLUMN EmployeeIdAhora la columna ''EmployeeId'' se eliminó de la tabla. Así que cambiaremos el nombre de la nueva columna ''EmployeeId_new'' recién agregada con ''EmployeeId''
sp_rename ''Employee.EmployeeId'', ''EmployeeId_new'', ''COLUMN''Para reorganizar la tabla en la misma forma en que se encontraba, debemos agregar la restricción de clave principal para la columna ''EmployeeId''
ALTER TABLE Empleado agregar restricción [PK_dbo.Employee] clave principal (EmployeeId)
8. Ahora, la tabla ''Empleado'' con ''EmpleadoId'' se modifica para las reglas de identidad junto con la restricción de clave principal existente
En SQL 2005 y versiones posteriores, hay un truco para resolver este problema sin cambiar las páginas de datos de la tabla. Esto es importante para tablas grandes donde tocar cada página de datos puede tomar minutos u horas. El truco también funciona incluso si la columna de identidad es una clave principal, es parte de un índice agrupado o no agrupado, u otros errores que pueden hacer tropezar con la solución más simple de "agregar / eliminar / renombrar columnas".
Aquí está el truco: puede usar la instrucción ALTER TABLE ... SWITCH de SQL Server para cambiar el esquema de una tabla sin cambiar los datos, lo que significa que puede reemplazar una tabla con una IDENTIDAD con un esquema de tabla idéntico, pero sin una columna IDENTIDAD. El mismo truco funciona para agregar IDENTIDAD a una columna existente.
Normalmente, ALTER TABLE ... SWITCH se usa para reemplazar de manera eficiente una partición completa en una tabla particionada con una nueva partición vacía. Pero también puede usarse en tablas no particionadas.
He utilizado este truco para convertir, en menos de 5 segundos, una columna de una tabla de 2.5 billones de filas de IDENTIDAD a una no IDENTIDAD (para ejecutar una consulta de varias horas cuyo plan de consulta funcionó mejor para la no IDENTIDAD columnas), y luego restauró la configuración de IDENTIDAD, nuevamente en menos de 5 segundos.
Aquí hay un ejemplo de código de cómo funciona.
CREATE TABLE Test
(
id int identity(1,1),
somecolumn varchar(10)
);
INSERT INTO Test VALUES (''Hello'');
INSERT INTO Test VALUES (''World'');
-- copy the table. use same schema, but no identity
CREATE TABLE Test2
(
id int NOT NULL,
somecolumn varchar(10)
);
ALTER TABLE Test SWITCH TO Test2;
-- drop the original (now empty) table
DROP TABLE Test;
-- rename new table to old table''s name
EXEC sp_rename ''Test2'',''Test'';
-- update the identity seed
DBCC CHECKIDENT(''Test'');
-- see same records
SELECT * FROM Test;
Esto obviamente es más complicado que las soluciones en otras respuestas, pero si su tabla es grande, esto puede ser un verdadero salvavidas. Hay algunas advertencias:
- Por lo que sé, la identidad es lo único que puede cambiar acerca de las columnas de su tabla con este método. No se permite agregar / eliminar columnas, cambiar la nulabilidad, etc.
- Tendrá que soltar las teclas delanteras antes de hacer el cambio y restaurarlas después.
- Lo mismo para las funciones WITH SCHEMABINDING, vistas, etc.
- los nuevos índices de la tabla deben coincidir exactamente (las mismas columnas, el mismo orden, etc.)
- Las tablas antiguas y nuevas deben estar en el mismo grupo de archivos.
- Solo funciona en SQL Server 2005 o posterior
- Anteriormente creía que este truco solo funciona en las ediciones Enterprise o Developer de SQL Server (porque las particiones solo son compatibles con las versiones Enterprise y Developer), pero Mason G. Zhwiti en su comentario a continuación dice que también funciona en la Edición estándar de SQL. Supongo que esto significa que la restricción a Enterprise o Developer no se aplica a ALTER TABLE ... SWITCH.
Hay un buen artículo en TechNet que detalla los requisitos anteriores.
ACTUALIZACIÓN: Eric Wu tenía un comentario a continuación que agrega información importante sobre esta solución. Copiándolo aquí para asegurarse de que reciba más atención:
Hay otra advertencia aquí que vale la pena mencionar. Aunque la nueva tabla recibirá felizmente los datos de la tabla anterior, y todas las filas nuevas se insertarán siguiendo un patrón de identidad, comenzarán en 1 y posiblemente se romperán si dicha columna es una clave principal. Considere ejecutar
DBCC CHECKIDENT(''<newTableName>'')inmediatamente después de cambiar. Consulte msdn.microsoft.com/en-us/library/ms176057.aspx para obtener más información.
Si la tabla se está extendiendo activamente con nuevas filas (lo que significa que no tiene mucho tiempo de inactividad entre agregar IDENTIDAD y agregar nuevas filas), en lugar de DBCC CHECKIDENT , querrá establecer manualmente el valor de semilla de identidad en el nuevo esquema de tabla. para ser más grande que la ID más grande existente en la tabla, por ejemplo, IDENTITY (2435457, 1) . Es posible que pueda incluir tanto el ALTER TABLE...SWITCH como el DBCC CHECKIDENT en una transacción (o no, no se han probado) esto) pero parece que establecer el valor semilla manualmente será más fácil y más seguro.
Obviamente, si no se agregan nuevas filas a la tabla (o solo se agregan ocasionalmente, como un proceso ETL diario), esta condición de carrera no se producirá, por lo que DBCC CHECKIDENT está bien.
En el servidor sql 2014 (no sé acerca de las versiones inferiores) puede hacer esto simplemente, usando la secuencia.
CREATE SEQUENCE sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;
ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name
Desde aquí: Secuencia como valor predeterminado para una columna
Haga clic derecho en el nombre de la tabla en el Explorador de objetos. Obtendrá algunas opciones. Haga clic en ''Diseño''. Se abrirá una nueva pestaña para esta tabla. Puede agregar la restricción de identidad aquí en ''Propiedades de columna''.
Hay una solución interesante que se describe aquí: SERVIDOR SQL - Agregar o quitar propiedades de identidad en la columna
En pocas palabras, edite manualmente su tabla en el Administrador de SQL, cambie la identidad, NO GUARDE los cambios, solo muestre el script que se creará para los cambios, cópielo y utilícelo más adelante.
Es un gran ahorro de tiempo, ya que (el script) contiene todas las claves externas, índices, etc. relacionados con la tabla que cambia. Escribiendo esto manualmente ... Dios no lo quiera.
La respuesta aceptada es incorrecta: puede modificar una columna existente para que sea una identidad siempre que no contenga ningún valor nulo. Después del cambio, la semilla de identidad comenzará en el máximo (columna) + 1.
Entonces, lo que realmente debe hacer primero es proporcionar valores para todos los valores nulos.
No creo que pueda alterar una columna existente para que sea una columna de identidad usando tsql. Sin embargo, puede hacerlo a través de la vista de diseño de Enterprise Manager.
Alternativamente, puede crear una nueva fila como la columna de identidad, eliminar la columna antigua y luego cambiar el nombre de su nueva columna.
ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY CLUSTERED
No hay uno, por desgracia; La propiedad IDENTITY pertenece a la tabla en lugar de a la columna.
La forma más fácil es hacerlo en la GUI, pero si esta no es una opción, puede ir por el camino de copiar los datos, eliminar la columna, volver a agregarla con identidad y volver a colocar los datos.
Vea here para una cuenta de golpe por golpe.
No puede alterar una columna para que sea una columna de IDENTIDAD. Lo que deberá hacer es crear una nueva columna que se defina como una IDENTIDAD desde el inicio, luego suelte la columna antigua y cambie el nombre de la nueva a la antigua.
ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)
ALTER TABLE (yourTable) DROP COLUMN OldColumnName
EXEC sp_rename ''yourTable.NewColumn'', ''OldColumnName'', ''COLUMN''
Bagazo
No puedes alterar las columnas existentes para la identidad.
Tienes 2 opciones,
Cree una nueva tabla con identidad y suelte la tabla existente
Crea una nueva columna con identidad y suelta la columna existente
Enfoque 1. ( Nueva tabla ) Aquí puede conservar los valores de datos existentes en la columna de identidad recién creada.
CREATE TABLE dbo.Tmp_Names
(
Id int NOT NULL
IDENTITY(1, 1),
Name varchar(50) NULL
)
ON [PRIMARY]
go
SET IDENTITY_INSERT dbo.Tmp_Names ON
go
IF EXISTS ( SELECT *
FROM dbo.Names )
INSERT INTO dbo.Tmp_Names ( Id, Name )
SELECT Id,
Name
FROM dbo.Names TABLOCKX
go
SET IDENTITY_INSERT dbo.Tmp_Names OFF
go
DROP TABLE dbo.Names
go
Exec sp_rename ''Tmp_Names'', ''Names''
Enfoque 2 ( Nueva columna ) No puede retener los valores de datos existentes en la columna de identidad recién creada. La columna de identidad contendrá la secuencia del número.
Alter Table Names
Add Id_new Int Identity(1, 1)
Go
Alter Table Names Drop Column ID
Go
Exec sp_rename ''Names.Id_new'', ''ID'', ''Column''
Consulte la siguiente publicación del Foro de Microsoft SQL Server para obtener más detalles:
Para modificar las propiedades de identidad de una columna:
- En Server Explorer, haga clic con el botón derecho en la tabla con las propiedades de identidad que desea modificar y haga clic en Abrir definición de tabla. La tabla se abre en el Diseñador de tablas.
- Desactive la casilla de verificación Permitir valores nulos para la columna que desea cambiar.
- En la pestaña Propiedades de columna, expanda la propiedad Especificación de identidad.
- Haga clic en la celda de la cuadrícula de la propiedad secundaria Es Identidad y elija Sí en la lista desplegable.
- Escriba un valor en la celda Identity Seed. Este valor se asignará a la primera fila de la tabla. El valor 1 será asignado por defecto.
Eso es todo, y funcionó para mí.
Por diseño, no hay una forma sencilla de activar o desactivar la función de identidad para una columna existente. La única forma limpia de hacerlo es crear una nueva columna y convertirla en una columna de identidad o crear una nueva tabla y migrar sus datos.
Si usamos SQL Server Management Studio para eliminar el valor de identidad en la columna "id", se crea una nueva tabla temporal, los datos se mueven a la tabla temporal, se elimina la tabla anterior y se cambia el nombre de la nueva tabla.
Use Management Studio para realizar el cambio y luego haga clic con el botón derecho en el diseñador y seleccione "Generar un script de cambio".
Verás que esto es lo que hace el servidor SQL en segundo plano.
Según mi condición actual, sigo este enfoque. Quiero dar identidad a una tabla principal después de insertar los datos a través de un script.
Como deseo adjuntar identidad, siempre comienza desde 1 hasta el final del recuento de registros que deseo.
--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)
--then add primary key to that column (exist option you can ignore)
IF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE object_id = OBJECT_ID(N''[dbo].[PK_tblProductPriceList]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblProductPriceList]''))
ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO
Esto creará la misma columna de clave primaria con identidad
Utilicé estos enlaces: https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/
Si el póster original realmente deseaba establecer una columna existente para que fuera la PRIMARY KEY de la tabla y en realidad no necesitaba que la columna fuera una columna de IDENTITY (dos cosas diferentes), esto se puede hacer mediante t-SQL con:
ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])
Observe el paréntesis alrededor del nombre de la columna después de la opción PRIMARY KEY .
Aunque esta publicación es antigua y supongo que los solicitantes lo necesitan, sentí que esta información adicional podría ser útil para los usuarios que se encuentran con este hilo, ya que creo que la conversación podría llevar a creer que una columna existente no puede configurarse como una clave primaria sin agregarla como una nueva columna primero que sería incorrecta.
Soy un desarrollador de Java que llegó a formar parte de un equipo sin un DBA y otro en el que, como desarrollador, no puedo obtener derechos de DBA. Me asignaron la tarea de mover un esquema completo entre dos bases de datos, así que sin tener un DBA, tuve que hacerlo y ejecutarlo ejecutando scripts, no pudiendo usar la GUI en SQL Server 2008 porque no tenía privilegios de administrador.
Todo se movió sin problemas, sin embargo, al ejecutar un procedimiento almacenado en el nuevo esquema.tabla, encontré que perdí el campo de identidad en una tabla. Comprobé dos veces el script que creó la tabla y estaba allí, sin embargo, SQL Server no lo obtuvo cuando ejecuté el script. Más tarde, un DBA me dijo que él había visto este mismo problema antes.
En cualquier caso, para SQL Server 2008, estos son los pasos que tomé para resolver esto y funcionaron, así que lo estoy publicando aquí con la esperanza de que sea de ayuda para alguien. Esto es lo que hice porque tuve dependencias de FK en otra tabla que hizo esto más difícil:
Utilicé esta consulta para verificar que faltaba la identidad y para ver las dependencias en la tabla.
1.) Encontrar estadísticas en una tabla:
exec sp_help ''dbo.table_name_old'';
2.) Cree una nueva tabla duplicada e idéntica, excepto agregue un campo de identidad en el campo PK donde había estado antes.
3.) Deshabilita la identidad para mover datos.
SET IDENTITY_INSERT dbo.table_name ON
4.) Transferir los datos.
INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT
field1, field2, etc...
FROM
dbo.table_name_old;
5.) Verificar que los datos están ahí.
SELECT * FROM dbo.table_name_new
6.) Reactivar la identidad.
SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF
7.) Este es el mejor script que encontré para obtener todas las relaciones de FK para verificar a qué tabla (s) se refiere la tabla original como dependencias y encontré muchas, ¡así que es un guardián!
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
ORDER BY ReferenceTableName;
8.) Asegúrese de tener todos los scripts PK y FK para todas las tablas involucradas, antes de este próximo paso.
9.) Puede hacer clic con el botón derecho en cada clave y crear una secuencia de comandos utilizando SQL Server 2008
10.) Quite los FK de la tabla de dependencias usando esta sintaxis:
ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]
11.) Soltar la tabla original:
DROP TABLE dbo.table_name_old;
13.) Los siguientes pasos se basan en los scripts que creó en SQL Server 2008 en el paso 9.
--Agregue el PK a la nueva tabla.
--Agregar el FK a la nueva tabla.
--Agregue los FK de nuevo a la tabla de dependencias.
14.) Verificar que todo sea correcto y completo. Utilicé la GUI para mirar las tablas.
15.) Cambie el nombre de la nueva tabla al nombre de las tablas originales.
exec sp_RENAME ''[Schema_Name.OldTableName]'' , ''[NewTableName]'';
Finalmente, ¡todo funcionó!
genera un script para todas las tablas con clave principal = bigint que no tienen un conjunto de identidad; esto devolverá una lista de scripts generados con cada tabla;
SET NOCOUNT ON;
declare @sql table(s varchar(max), id int identity)
DECLARE @table_name nvarchar(max),
@table_schema nvarchar(max);
DECLARE vendor_cursor CURSOR FOR
SELECT
t.name, s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE EXISTS (
SELECT
[c].[name]
from sys.columns [c]
join sys.types [y] on [y].system_type_id = [c].system_type_id
where [c].[object_id] = [t].[object_id] and [y].name = ''bigint'' and [c].[column_id] = 1
) and NOT EXISTS
(
SELECT 1 FROM sys.identity_columns
WHERE [object_id] = t.[object_id]
) and exists (
select 1 from sys.indexes as [i]
inner join sys.index_columns as [ic] ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
where object_name([ic].[object_id]) = [t].[name]
)
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor
INTO @table_name, @table_schema
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @sql
declare @pkname varchar(100),
@pkcol nvarchar(100)
SELECT top 1
@pkname = i.name,
@pkcol = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM sys.indexes AS [i]
INNER JOIN sys.index_columns AS [ic] ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) = @table_name
declare @q nvarchar(max) = ''SELECT ''+@pkcol+'' FROM [''+@table_schema+''].[''+@table_name+''] ORDER BY ''+@pkcol+'' DESC''
DECLARE @ident_seed nvarchar(max) -- Change this to the datatype that you are after
SET @q = REPLACE(@q, ''SELECT'', ''SELECT TOP 1 @output = '')
EXEC sp_executeSql @q, N''@output bigint OUTPUT'', @ident_seed OUTPUT
insert into @sql(s) values (''BEGIN TRANSACTION'')
insert into @sql(s) values (''BEGIN TRY'')
-- create statement
insert into @sql(s) values (''create table [''+@table_schema+''].['' + @table_name + ''_Temp] ('')
-- column list
insert into @sql(s)
select
'' [''+[c].[name]+''] '' +
y.name +
(case when [y].[name] like ''%varchar'' then
coalesce(''(''+(case when ([c].[max_length] < 0 or [c].[max_length] >= 1024) then ''max'' else cast([c].max_length as varchar) end)+'')'','''')
else '''' end)
+ '' '' +
case when [c].name = @pkcol then ''IDENTITY('' +COALESCE(@ident_seed, ''1'')+'',1)'' else '''' end + '' '' +
( case when c.is_nullable = 0 then ''NOT '' else '''' end ) + ''NULL '' +
coalesce(''DEFAULT (''+(
REPLACE(
REPLACE(
LTrim(
RTrim(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
LTrim(
RTrim(
REPLACE(
REPLACE(
object_definition([c].default_object_id)
,'' '',''~'')
,'')'','' '')
)
)
,'' '',''*'')
,''~'','' '')
,'' '',''~'')
,''('','' '')
)
)
,'' '',''*'')
,''~'','' '')
) +
case when object_definition([c].default_object_id) like ''%get%date%'' then ''()'' else '''' end
+
'')'','''') + '',''
from sys.columns c
JOIN sys.types y ON y.system_type_id = c.system_type_id
where OBJECT_NAME(c.[object_id]) = @table_name and [y].name != ''sysname''
order by [c].column_id
update @sql set s=left(s,len(s)-1) where id=@@identity
-- closing bracket
insert into @sql(s) values( '')'' )
insert into @sql(s) values( ''SET IDENTITY_INSERT [''+@table_schema+''].[''+@table_name+''_Temp] ON'')
declare @cols nvarchar(max)
SELECT @cols = STUFF(
(
select '',[''+c.name+'']''
from sys.columns c
JOIN sys.types y ON y.system_type_id = c.system_type_id
where c.[object_id] = OBJECT_ID(@table_name)
and [y].name != ''sysname''
and [y].name != ''timestamp''
order by [c].column_id
FOR XML PATH ('''')
)
, 1, 1, '''')
insert into @sql(s) values( ''IF EXISTS(SELECT * FROM [''+@table_schema+''].[''+@table_name+''])'')
insert into @sql(s) values( ''EXEC(''''INSERT INTO [''+@table_schema+''].[''+@table_name+''_Temp] (''+@cols+'')'')
insert into @sql(s) values( ''SELECT ''+@cols+'' FROM [''+@table_schema+''].[''+@table_name+'']'''')'')
insert into @sql(s) values( ''SET IDENTITY_INSERT [''+@table_schema+''].[''+@table_name+''_Temp] OFF'')
insert into @sql(s) values( ''DROP TABLE [''+@table_schema+''].[''+@table_name+'']'')
insert into @sql(s) values( ''EXECUTE sp_rename N''''[''+@table_schema+''].[''+@table_name+''_Temp]'''', N''''''+@table_name+'''''', ''''OBJECT'''''')
if ( @pkname is not null ) begin
insert into @sql(s) values(''ALTER TABLE [''+@table_schema+''].[''+@table_name+''] ADD CONSTRAINT [''+@pkname+''] PRIMARY KEY CLUSTERED ('')
insert into @sql(s)
select '' [''+COLUMN_NAME+''] ASC,'' from information_schema.key_column_usage
where constraint_name = @pkname
GROUP BY COLUMN_NAME, ordinal_position
order by ordinal_position
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
insert into @sql(s) values ('' )'')
end
insert into @sql(s) values (''--Run your Statements'')
insert into @sql(s) values (''COMMIT TRANSACTION'')
insert into @sql(s) values (''END TRY'')
insert into @sql(s) values (''BEGIN CATCH'')
insert into @sql(s) values ('' ROLLBACK TRANSACTION'')
insert into @sql(s) values ('' DECLARE @Msg NVARCHAR(MAX) '')
insert into @sql(s) values ('' SELECT @Msg=ERROR_MESSAGE() '')
insert into @sql(s) values ('' RAISERROR(''''Error Occured: %s'''', 20, 101,@msg) WITH LOG'')
insert into @sql(s) values (''END CATCH'')
declare @fqry nvarchar(max)
-- result!
SELECT @fqry = (select char(10) + s from @sql order by id FOR XML PATH (''''))
SELECT @table_name as [Table_Name], @fqry as [Generated_Query]
PRINT ''Table: ''+@table_name
EXEC sp_executeSql @fqry
FETCH NEXT FROM vendor_cursor
INTO @table_name, @table_schema
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
no puede hacerlo así, necesita agregar otra columna, eliminar la columna original y cambiar el nombre de la nueva columna o crear una nueva tabla, copiar los datos y eliminar la tabla anterior y cambiar el nombre de la nueva tabla a la antigua mesa
Si usa SSMS y configura la propiedad de identidad en ON en el diseñador, esto es lo que hace SQL Server entre bambalinas. Entonces, si tiene una tabla llamada [usuario], esto es lo que sucede si crea una ID de usuario y una identidad.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
GO
CREATE TABLE dbo.Tmp_User
(
UserID int NOT NULL IDENTITY (1, 1),
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
MiddleInitial char(1) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
EXEC(''INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX'')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO
GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N''dbo.Tmp_User'', N''User'', ''OBJECT''
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
PK_User PRIMARY KEY CLUSTERED
(
UserID
) ON [PRIMARY]
GO
COMMIT
Una vez dicho esto, hay una manera de hackear la tabla del sistema para lograrlo estableciendo el valor a nivel de bits pero no es compatible y no lo haría
Explicación simple
Renombra la columna existente usando sp_RENAME
EXEC sp_RENAME ''Table_Name.Existing_ColumnName'', ''New_ColumnName'', ''COLUMN''
Ejemplo para Renombrar:
La columna existente UserID se renombra como OldUserID
EXEC sp_RENAME ''AdminUsers.UserID'' , ''OldUserID'', ''COLUMN''
Luego, agregue una nueva columna usando la consulta de modificación para establecer como clave principal y valor de identidad
ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)
Ejemplo para establecer clave principal
El nuevo nombre de columna creado es UserID
ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
luego suelta la columna renombrada
ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName
Ejemplo para soltar la columna renombrada
ALTER TABLE Users DROP COLUMN OldUserID
Ahora hemos agregado una clave principal e identidad a la columna existente en la tabla.