Добавление идентификатора в существующий столбец
Мне нужно изменить первичный ключ таблицы на столбец идентификаторов, и в таблице уже есть ряд строк.
У меня есть скрипт для очистки идентификаторов, чтобы они были последовательными, начиная с 1, отлично работает в моей тестовой базе данных.
Какова команда SQL, чтобы изменить столбец, чтобы иметь свойство идентификации?
23 ответа
Вы не можете изменить существующие столбцы для идентификации.
У вас есть 2 варианта,
Создать новую таблицу с идентификатором и удалить существующую таблицу
Создать новый столбец с идентификатором и удалить существующий столбец
Подход 1. (Новая таблица) Здесь вы можете сохранить существующие значения данных во вновь созданном столбце идентификаторов.
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'
Подход 2 (Новый столбец) Нельзя сохранить существующие значения данных во вновь созданном столбце идентификаторов. Столбец идентификаторов будет содержать последовательность чисел.
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'
Для получения дополнительной информации см. Следующий пост на форуме по Microsoft SQL Server:
В SQL 2005 и выше есть хитрость для решения этой проблемы без изменения страниц данных таблицы. Это важно для больших таблиц, где прикосновение к каждой странице данных может занять минуты или часы. Трюк также работает, даже если столбец идентификаторов является первичным ключом, является частью кластерного или некластеризованного индекса или других ошибок, которые могут привести к путанице в более простом решении "добавить / удалить / переименовать столбец".
Вот хитрость: вы можете использовать оператор SQL Server ALTER TABLE...SWITCH, чтобы изменить схему таблицы без изменения данных, то есть вы можете заменить таблицу идентификатором IDENTITY идентичной схемой таблицы, но без столбца IDENTITY. Тот же трюк работает для добавления IDENTITY в существующий столбец.
Обычно ALTER TABLE...SWITCH используется для эффективной замены полного раздела в многораздельной таблице новым пустым разделом. Но его также можно использовать и в однораздельных таблицах.
Я использовал этот трюк для преобразования менее чем за 5 секунд столбца таблицы из 2,5 миллиардов строк из IDENTITY в не-IDENTITY (для того, чтобы выполнить многочасовой запрос, план запроса которого работал лучше для не-IDENTITY столбцы), а затем восстановил настройку IDENTITY, снова менее чем за 5 секунд.
Вот пример кода того, как это работает.
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;
Это, очевидно, более сложное решение, чем в других ответах, но если у вас большой стол, это может реально спасти жизнь. Есть несколько предостережений:
- Насколько я знаю, идентичность - это единственное, что вы можете изменить в столбцах таблицы с помощью этого метода. Добавление / удаление столбцов, изменение обнуляемости и т. Д. Запрещено.
- Вам нужно будет сбросить чужие ключи, прежде чем переключаться, и восстанавливать их после.
- То же самое для функций WITH SCHEMABINDING, представлений и т. Д.
- индексы новой таблицы должны точно совпадать (те же столбцы, тот же порядок и т. д.)
- Старые и новые таблицы должны быть в одной файловой группе.
- Работает только на SQL Server 2005 или более поздней версии
- Ранее я полагал, что этот прием работает только в редакциях SQL Server для предприятий или разработчиков (поскольку разделы поддерживаются только в версиях для предприятий и разработчиков), но Мейсон Дж. Жвити в своем комментарии ниже говорит, что он также работает и в SQL Standard Edition. Я предполагаю, что это означает, что ограничение Enterprise или Developer не распространяется на ALTER TABLE...SWITCH.
На TechNet есть хорошая статья, подробно описывающая требования выше.
ОБНОВЛЕНИЕ - Eric Wu был ниже комментарий, который добавляет важную информацию об этом решении. Скопируйте его сюда, чтобы убедиться, что он привлекает больше внимания:
Здесь стоит упомянуть еще одну оговорку. Хотя новая таблица будет успешно получать данные из старой таблицы, и все новые строки будут вставлены в соответствии с шаблоном идентификации, они начнутся с 1 и, возможно, сломаются, если указанный столбец является первичным ключом. Рассмотрим бег
DBCC CHECKIDENT('<newTableName>')сразу после переключения. См. http://msdn.microsoft.com/en-us/library/ms176057.aspx для получения дополнительной информации.
Если таблица активно расширяется новыми строками (то есть у вас не так много простоев между добавлением IDENTITY и добавлением новых строк, тогда вместо DBCC CHECKIDENT вам нужно будет вручную установить начальное значение идентификатора в новой схеме таблицы, чтобы оно было больше, чем самый большой существующий идентификатор в таблице, например IDENTITY (2435457, 1), Вы можете включить как ALTER TABLE...SWITCH и DBCC CHECKIDENT в транзакции (или нет - не проверял это), но кажется, что установка начального значения вручную будет проще и безопаснее.
Очевидно, что если в таблицу не добавляются новые строки (или они добавляются только изредка, например, ежедневный процесс ETL), то это условие гонки не произойдет, поэтому DBCC CHECKIDENT Это хорошо.
Вы не можете изменить столбец как столбец IDENTITY. Что вам нужно сделать, это создать новый столбец, который определяется как IDENTITY с самого начала, затем отбросить старый столбец и переименовать новый в старое имя.
ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)
ALTER TABLE (yourTable) DROP COLUMN OldColumnName
EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'
Марк
Попробуйте использовать ПОСЛЕДОВАТЕЛЬНОСТЬ вместо ИДЕНТИЧНОСТИ.
В SQL Server 2014 (я не знаю о более низких версиях) вы можете сделать это просто, используя последовательность.
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
Отсюда: последовательность в качестве значения по умолчанию для столбца
Вот отличное решение, описанное здесь: SQL SERVER - Добавить или удалить свойство идентификации в столбце
Короче говоря, отредактируйте вручную свою таблицу в SQL Manager, поменяйте личность, НЕ СОХРАНЯЙТЕ изменения, просто покажите сценарий, который будет создан для изменений, скопируйте его и используйте позже.
Это значительно экономит время, потому что он (скрипт) содержит все внешние ключи, индексы и т. Д., Связанные с таблицей, которую вы меняете. Пишу это вручную... не дай бог.
Простое объяснение
Переименуйте существующий столбец, используя sp_RENAME
EXEC sp_RENAME 'Table_Name.Existing_ColumnName', 'New_ColumnName', 'COLUMN'
Пример для переименования:
Существующий столбец UserID переименован в OldUserID
EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'
Затем добавьте новый столбец, используя запрос alter, чтобы установить в качестве первичного ключа и значения идентификатора
ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)
Пример установки первичного ключа
Имя нового созданного столбца - UserID
ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
затем бросьте переименованную колонку
ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName
Пример для отброшенного переименованного столбца
ALTER TABLE Users DROP COLUMN OldUserID
Теперь мы добавляем первичный ключ и идентификатор в существующий столбец таблицы.
Я Java-разработчик, который случайно попал в команду без администратора и не могу получить права администратора. Передо мной была поставлена задача переместить всю схему между двумя базами данных, поэтому без администратора базы данных мне пришлось делать это и делать это с помощью сценариев, поскольку я не мог использовать графический интерфейс в SQL Server 2008, потому что у меня не было прав администратора.
Все было перемещено без проблем, однако при запуске хранимой процедуры в новой schema.table я обнаружил, что потерял поле идентификатора в таблице. Я дважды проверил скрипт, который создал таблицу, и он был там, однако, SQL Server не получил его, когда я запустил скрипт. Позже мне сказал администратор БД, что он видел эту же проблему раньше.
В любом случае для SQL Server 2008 я предпринял следующие шаги, чтобы решить эту проблему, и они сработали, поэтому я публикую это здесь в надежде, что это кому-нибудь поможет. Это то, что я сделал, так как у меня были зависимости FK от другой таблицы, что усложняло это:
Я использовал этот запрос, чтобы убедиться, что идентичность действительно отсутствует, и просмотреть зависимости от таблицы.
1.) Найти статистику по таблице:
exec sp_help 'dbo.table_name_old';
2.) Создайте дубликат идентичной новой таблицы, за исключением того, что добавьте поле идентификатора в поле PK, где оно было раньше.
3.) Отключить удостоверение для перемещения данных.
SET IDENTITY_INSERT dbo.table_name ON
4.) Передача данных.
INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT
field1, field2, etc...
FROM
dbo.table_name_old;
5.) Убедитесь, что данные есть.
SELECT * FROM dbo.table_name_new
6.) Повторно включите личность.
SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF
7.) Это лучший скрипт, который я нашел, чтобы получить все отношения FK, чтобы проверить, какие таблицы (таблицы) исходная таблица ссылается как на зависимости, и я сталкивался со многими, так что это хранитель!
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.) Перед этим следующим шагом убедитесь, что у вас есть все сценарии PK и FK для всех задействованных таблиц.
9.) Вы можете щелкнуть правой кнопкой мыши по каждому ключу и написать скрипт, используя SQL Server 2008
10.) Удалите FK из таблицы зависимостей, используя этот синтаксис:
ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]
11.) Оставьте оригинальный стол:
DROP TABLE dbo.table_name_old;
13.) Следующие шаги основаны на сценариях, которые вы создали в SQL Server 2008 на шаге 9.
- Добавить ПК к новой таблице.
- Добавить FK к новой таблице.
- Добавить FK обратно в таблицу зависимостей.
14.) Убедитесь, что все правильно и полно. Я использовал графический интерфейс для просмотра таблиц.
15.) Переименуйте новую таблицу в исходное имя таблицы.
exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';
Наконец-то все заработало!
Как я понял в обычных случаях, мы создаем таблицу с первичным ключом, который имеет свойство Identity
Поэтому переименовать или удалить столбец, связанный с ограничением первичного ключа, будет невозможно, поскольку правила ограничения проверяют структуру столбца.
Чтобы достичь этого, мы должны обработать некоторые шаги следующим образом:
Давайте предположим, что TableName = 'Employee' и ColumnName = 'EmployeeId'
1. Добавьте новый столбец EmployeeId_new в таблицу Employee.
ALTER TABLE Сотрудник ДОБАВИТЬ EmployeeId_new INT IDENTITY(1,1)
Теперь удалите столбец "EmployeeId" из таблицы "Employee"
ALTER TABLE Employee DROP COLUMN EmployeeIdЭто вызовет ошибку, потому что правила ограничения первичного ключа применимы и проверяют структуру столбца.
* ### 'Сообщение 5074, уровень 16, состояние 1, строка 1 Объект [PK_dbo.Employee] зависит от столбца [EmployeeId].'###Таким образом, мы должны сначала удалить ограничение первичного ключа из таблицы "Сотрудник", а затем удалить столбец.
ALTER TABLE Ограничение DROP сотрудника [PK_dbo.Employee]Теперь мы можем удалить столбец EmployeeId из таблицы Employee, как это делалось на предыдущем шаге, где мы получили ошибку
ALTER TABLE Employee DROP COLUMN EmployeeIdТеперь столбец EmployeeId удален из таблицы. Таким образом, мы переименуем новый добавленный новый столбец EmployeeId_new в EmployeeId.
sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'Чтобы переставить таблицу в ту же форму, в которой она была, нам нужно добавить ограничение первичного ключа для столбца EmployeeId
ALTER TABLE Сотрудник добавить ограничение [PK_dbo.Employee] первичный ключ (EmployeeId)
8. Теперь таблица "Employee" с "EmployeeId" модифицируется для правил идентификации вместе с существующим ограничением первичного ключа.
Вы не можете сделать это так, вам нужно добавить другой столбец, удалить исходный столбец и переименовать новый столбец или создать новую таблицу, скопировать данные и удалить старую таблицу, а затем переименовать новую таблицу в старую. Таблица
Если вы используете SSMS и устанавливаете свойство идентификатора на ON в конструкторе, то вот что SQL Server делает за кулисами. Так что если у вас есть таблица с именем [user], это то, что происходит, если вы делаете UserID и идентификатор
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
Сказав, что есть способ взломать системную таблицу, чтобы сделать это, установив побитовое значение, но это не поддерживается, и я бы не стал этого делать
Если вы используете Visual Studio 2017+
- В обозревателе объектов сервера щелкните правой кнопкой мыши таблицу и выберите "просмотреть код"
- Добавьте модификатор "IDENTITY" в ваш столбец
- Обновить
Это сделает все для вас.
По замыслу не существует простого способа включить или отключить функцию идентификации для существующего столбца. Единственный чистый способ сделать это - создать новый столбец и сделать его столбцом идентификаторов или создать новую таблицу и перенести ваши данные.
Если мы используем SQL Server Management Studio, чтобы избавиться от значения идентификатора в столбце "id", создается новая временная таблица, данные перемещаются во временную таблицу, старая таблица удаляется, а новая таблица переименовывается.
Используйте Management Studio, чтобы внести изменения, а затем щелкните правой кнопкой мыши в конструкторе и выберите "Создать сценарий изменения".
Вы увидите, что это то, что SQL-сервер делает в фоновом режиме.
Принятый ответ неверен: вы можете изменить существующий столбец на тождественный, если он не содержит нулевых значений. После изменения начальное значение идентификатора начнется с максимума (столбец) + 1.
Итак, что вам действительно нужно сделать в первую очередь, это указать значения для всех нулей.
Щелкните правой кнопкой мыши имя таблицы в обозревателе объектов. Вы получите несколько вариантов. Нажмите на "Дизайн". Новая таблица будет открыта для этой таблицы. Вы можете добавить ограничение Identity здесь, в "Свойствах столбца".
Чтобы изменить свойства идентификатора для столбца:
- В обозревателе серверов щелкните правой кнопкой мыши таблицу со свойствами идентификаторов, которые вы хотите изменить, и выберите Открыть определение таблицы. Таблица открывается в конструкторе таблиц.
- Снимите флажок "Разрешить пустые значения" для столбца, который вы хотите изменить.
- На вкладке Свойства столбца разверните свойство Спецификация идентификации.
- Щелкните ячейку сетки для дочернего свойства Is Identity и выберите "Да" в раскрывающемся списке.
- Введите значение в ячейку Identity Seed. Это значение будет присвоено первой строке таблицы. Значение 1 будет назначено по умолчанию.
Вот и все, и это сработало для меня
В моем случае было много таблиц без идентификаторов, поэтому копирование и воссоздание заняло бы слишком много времени. Я нашел более простой способ сделать это, если вы используете SQL Server Management Studio:
- Откройте Инструменты -> Параметры -> Конструкторы -> Конструкторы таблиц и баз данных .
- Снимите флажок « Запретить сохранение изменений, препятствующих повторному созданию таблицы », чтобы не удалять таблицу и не создавать ее заново при изменении дизайна таблицы.
- Сохраните изменения.
- Щелкните правой кнопкой мыши по таблице и выберите «Дизайн» .
- Выберите столбец, откройте свойства и выберите « Да » в раскрывающемся списке «Идентификация» .
Таблица не удаляется, а данные внутри таблицы и зависимости остаются прежними.
Если первоначальный постер действительно хотел установить существующий столбец как PRIMARY KEY для таблицы и на самом деле не нужен столбец, чтобы быть IDENTITY столбец (две разные вещи), то это можно сделать через t-SQL с помощью:
ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])
Обратите внимание на круглые скобки вокруг имени столбца после PRIMARY KEY вариант.
Хотя этот пост старый, и я делаю предположение о необходимости запрашивающих, я чувствовал, что эта дополнительная информация может быть полезна для пользователей, сталкивающихся с этой веткой, так как я считаю, что разговор может привести к тому, что существующий столбец не может быть установлен как первичный ключ без добавления его в качестве нового столбца, что будет неправильно.
К сожалению, его нет; свойство IDENTITY принадлежит таблице, а не столбцу.
Проще всего сделать это в графическом интерфейсе, но если это не вариант, вы можете пройти долгий путь, скопировав данные, отбросив столбец, повторно добавив его с идентификатором и вернув данные обратно.
Смотрите здесь для подробного описания.
Я не верю, что вы можете изменить существующий столбец на столбец идентификаторов, используя tsql. Однако вы можете сделать это через представление конструктора Enterprise Manager.
В качестве альтернативы вы можете создать новую строку в качестве столбца идентификаторов, удалить старый столбец, а затем переименовать новый столбец.
ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY CLUSTERED
Согласно моему нынешнему состоянию, я следую этому подходу. Я хочу присвоить первичную таблицу идентификаторам после вставки данных через скрипт.
Как я хочу добавить идентичность, так что она всегда начинается с 1 до конца количества записей, которое я хочу.
--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
Это создаст тот же столбец первичного ключа с идентичностью
Я использовал эти ссылки: https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/
изменить таблицу [Человек] добавить ID int первичный ключ IDENTITY (1,1)
Это добавит первичный ключ и начальное значение отступа и заполнит новый столбец.
Этот ответ очень похож на Этот ответ , с небольшими отличиями.
Например, у вас есть 3 таких таблицы с двумя внешними ключами.
ДДЛ:
CREATE TABLE [Table_1]
(
[id] [int] NOT NULL PRIMARY KEY,
[Name] [varchar](10) NULL
)
GO
CREATE UNIQUE NONCLUSTERED INDEX Table_1_Index
ON [Table_1] ([Name]);
CREATE TABLE [Table_2]
(
[id] [int] NOT NULL
FOREIGN KEY REFERENCES [Table_1]([id]) ON UPDATE CASCADE,
[car] [varchar](10) NULL
)
GO
CREATE TABLE [Table_3]
(
[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [varchar](10) NULL
FOREIGN KEY REFERENCES [Table_1]([Name])
)
GO
INSERT INTO [Table_1]
VALUES (10000, 'John'), (10001, 'Mike')
INSERT INTO [Table_2]
VALUES (10000, 'Benz'), (10000, 'Volvo'), (10001, 'BMW')
INSERT INTO [Table_3]
VALUES ('John')
GO
Если вы хотите добавить индивидуальность вidстолбец вTable_1сdboсхемы и хотите начать с 10000 с шагом 1, выполните этот запрос:
DECLARE @Schema SysName = 'dbo'
DECLARE @TableName SysName = 'Table_1'
DECLARE @ColumnName SysName = 'id'
DECLARE @IdentityType VARCHAR(20) = 'IDENTITY(10000,1)'
DECLARE @Table_Name SYSNAME = QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName)
DECLARE @SQLCreateTable NVARCHAR(MAX)
DECLARE @SQLFK NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
--get foreign keys
;WITH fk_columns AS
(
SELECT
constraint_object_id,
OBJECT_NAME(constraint_object_id) KeyName,
OBJECT_NAME(fkc.parent_object_id) FKTable,
OBJECT_NAME(fkc.referenced_object_id) PKTable,
COL_NAME(fkc.parent_object_id, parent_column_id) FKColumn,
COL_NAME(fkc.referenced_object_id, referenced_column_id) PKColumn,
delete_referential_action,
update_referential_action,
is_not_trusted
FROM
sys.foreign_key_columns fkc
JOIN
sys.foreign_keys fk ON fk.object_id = fkc.constraint_object_id
WHERE
fkc.referenced_object_id = OBJECT_ID(@Table_Name)
)
SELECT
@SQLFK = ISNULL((SELECT (
SELECT CHAR(13) +
'ALTER TABLE ' + fk.FKTable + ' WITH'
+ CASE WHEN fk.is_not_trusted = 1
THEN ' NOCHECK'
ELSE ' CHECK'
END +
' ADD CONSTRAINT [' + fk.KeyName + '] FOREIGN KEY('
+ STUFF((
SELECT
', [' + k.FKColumn + ']'
FROM
fk_columns k
WHERE
k.constraint_object_id = fk.constraint_object_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')' +
' REFERENCES '+@Table_Name+' ('
+ STUFF((
SELECT
', [' + k.PKColumn + ']'
FROM
fk_columns k
WHERE
k.constraint_object_id = fk.constraint_object_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')'
+ CASE
WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'
WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT'
ELSE ''
END
+ CASE
WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'
ELSE ''
END
+ CHAR(13) + 'ALTER TABLE ' + fk.FKTable + ' CHECK CONSTRAINT [' + fk.KeyName + ']' + CHAR(13)
FROM fk_columns fk WITH (NOWAIT)
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
--drop foreign keys
;WITH fk_columns AS
(
SELECT
OBJECT_NAME(constraint_object_id) KeyName,
OBJECT_NAME(fkc.parent_object_id) FKTable
FROM
sys.foreign_key_columns fkc
JOIN
sys.foreign_keys fk ON fk.object_id = fkc.constraint_object_id
WHERE
fkc.referenced_object_id = OBJECT_ID(@Table_Name)
)
SELECT @SQL = ISNULL((SELECT (
SELECT
CHAR(13) + 'ALTER TABLE ' + fk.FKTable +
' DROP CONSTRAINT [' + fk.KeyName + ']' + CHAR(13)
FROM
fk_columns fk WITH (NOWAIT)
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
EXECUTE sp_executesql @SQL
--find not exist table name
DECLARE @i INT = 1
WHILE OBJECT_ID(QUOTENAME(@Schema) + '.' +QUOTENAME(@TableName) + '_Temp' + CAST(@i AS varchar), 'U') IS NOT NULL
SET @i += 1
DECLARE @TempName SysName = @TableName + '_Temp' + CAST(@i AS VARCHAR)
DECLARE @Temp_Name SysName = QUOTENAME(@Schema) + '.' +QUOTENAME(@TableName + '_Temp' + CAST(@i AS VARCHAR))
--create temp table like table code
DECLARE @object_id INT = OBJECT_ID(@Table_Name)
;WITH index_column AS
(
SELECT
ic.[object_id],
ic.index_id,
ic.is_descending_key,
ic.is_included_column,
c.name
FROM
sys.index_columns ic WITH (NOWAIT)
JOIN
sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
WHERE
ic.[object_id] = @object_id
),
fk_columns AS
(
SELECT
k.constraint_object_id,
cname = c.name,
rcname = rc.name
FROM
sys.foreign_key_columns k WITH (NOWAIT)
JOIN
sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id
JOIN
sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
WHERE
k.parent_object_id = @object_id
)
SELECT @SQLCreateTable = 'CREATE TABLE ' + @Temp_Name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT
CHAR(9) + ', [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + cc.[definition]
ELSE UPPER(tp.name) +
CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name IN ('decimal', 'numeric')
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
CASE WHEN c.name = @ColumnName THEN ' ' +@IdentityType ELSE '' END +
CASE WHEN c.is_nullable = 1 and c.name <> @ColumnName THEN ' NULL' ELSE ' NOT NULL' END +
CASE WHEN dc.[definition] IS NOT NULL and c.name <> @ColumnName THEN ' DEFAULT' + dc.[definition] ELSE '' END
END + CHAR(13)
FROM
sys.columns c WITH (NOWAIT)
JOIN
sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
LEFT JOIN
sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
LEFT JOIN
sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
LEFT JOIN
sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE
c.[object_id] = @object_id
ORDER BY
c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
+ ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' +
(SELECT STUFF((
SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
FROM
sys.index_columns ic WITH (NOWAIT)
JOIN
sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE
ic.is_included_column = 0
AND ic.[object_id] = k.parent_object_id
AND ic.index_id = k.unique_index_id
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
+ ')' + CHAR(13)
FROM
sys.key_constraints k WITH (NOWAIT)
WHERE
k.parent_object_id = @object_id
AND k.[type] = 'PK'), '') + ')' + CHAR(13)
+ ISNULL((SELECT (
SELECT CHAR(13) +
'ALTER TABLE ' + @Temp_Name + ' WITH'
+ CASE WHEN fk.is_not_trusted = 1
THEN ' NOCHECK'
ELSE ' CHECK'
END +
' ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY('
+ STUFF((
SELECT
', [' + k.cname + ']'
FROM
fk_columns k
WHERE
k.constraint_object_id = fk.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')' +
' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
+ STUFF((
SELECT
', [' + k.rcname + ']'
FROM
fk_columns k
WHERE
k.constraint_object_id = fk.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')'
+ CASE
WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'
WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT'
ELSE ''
END
+ CASE
WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'
ELSE ''
END
+ CHAR(13) + 'ALTER TABLE ' + @Temp_Name + ' CHECK CONSTRAINT [' + fk.name + ']' + CHAR(13)
FROM sys.foreign_keys fk WITH (NOWAIT)
JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
WHERE fk.parent_object_id = @object_id
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
+ ISNULL(((SELECT
CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END
+ CASE i.type WHEN 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED' END
+ ' INDEX [' + i.name + '] ON ' + @Temp_Name + ' (' +
STUFF((
SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
FROM
index_column c
WHERE
c.is_included_column = 0
AND c.index_id = i.index_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
+ ISNULL(CHAR(13) + 'INCLUDE (' +
STUFF((
SELECT
', [' + c.name + ']'
FROM
index_column c
WHERE
c.is_included_column = 1
AND c.index_id = i.index_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + CHAR(13)
FROM
sys.indexes i WITH (NOWAIT)
WHERE
i.[object_id] = @object_id
AND i.is_primary_key = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
), '')
--rename primary key constaint on column from table
DECLARE @PrimaryName SYSNAME
SELECT @PrimaryName=i.name
FROM
sys.index_columns ic WITH (NOWAIT)
JOIN
sys.indexes i WITH (NOWAIT) ON i.object_id = ic.object_id and i.index_id = ic.index_id
WHERE
i.object_id = OBJECT_ID(@Table_Name) and Col_Name(ic.object_id, column_id) = @ColumnName and is_primary_key = 1
SET @SQL = N'EXEC sp_rename ' + @PrimaryName + ', ' + 'ThisMustNotBeExist'+ @PrimaryName + ', N''OBJECT'''
EXECUTE sp_executesql @SQL
--rename index on column from table
DECLARE @IndexName SYSNAME
SELECT
@IndexName = i.name
FROM
sys.index_columns ic WITH (NOWAIT)
JOIN
sys.indexes i WITH (NOWAIT) ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE
i.object_id = OBJECT_ID(@Table_Name)
AND Col_Name(ic.object_id, column_id) = @ColumnName
AND is_primary_key = 0
SET @SQL = N'EXEC sp_rename ' + @Table_Name + '.' + @IndexName + ', ' + 'ThisMustNotBeExist'+ @IndexName + ', N''INDEX'''
EXECUTE sp_executesql @SQL
--run create temp table like table code
EXECUTE sp_executesql @SQLCreateTable
--Move table data to temp data
SET @SQL = N'ALTER TABLE '+ @Table_Name + ' SWITCH TO ' + @Temp_Name
EXECUTE sp_executesql @SQL
--check clean up
SET @SQL = N'IF EXISTS (SELECT * FROM '+ @Table_Name + ') THROW 50000, ''Table is not clear'', 1'
EXECUTE sp_executesql @SQL
--drop table
SET @SQL = N'DROP TABLE '+ @Table_Name
EXECUTE sp_executesql @SQL
--rename temp table to table
EXEC sys.sp_rename @Temp_Name, @TableName, 'OBJECT';
--create foreign keys
EXECUTE sp_executesql @SQLFK
DBCC CHECKIDENT(@TableName, RESEED)
Примечание
- Этот код копирует только ключи и индексы. Если у вас есть другие вещи в этой таблице, вы потеряете их после запуска этого кода.
Если вы также хотите копировать триггеры или расширенное свойство таблицы, проверьте этот ответ и добавьте@SQL2и@SQL3к@SQLCreateTableпрежде чем выполнить его
В основном есть четыре логических шага.
Создайте новый столбец Identity. Включите Вставить идентификацию для этого нового столбца.
Вставьте данные из исходного столбца (столбец, который вы хотите преобразовать в личность) в этот новый столбец.
Отключите вставку идентификатора для нового столбца.
Удалите исходный столбец и переименуйте новый столбец в имя исходного столбца.
Там могут быть некоторые сложности, такие как работа на нескольких серверах и т. Д.
Пожалуйста, обратитесь к следующей статье за шагами (используя ssms & T-sql). Эти шаги предназначены для начинающих с меньшим контролем T-SQL.
Генерирует скрипт для всех таблиц с первичным ключом = bigint, у которых нет набора идентификаторов; это вернет список сгенерированных скриптов с каждой таблицей;
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;