I need to change the primary key of a table to an identity column, and there's already a number of rows in table.
I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database.
What's the SQL command to alter the column to have an identity property?
You can't alter the existing columns for identity.
You have 2 options,
Create a new table with identity & drop the existing table
Create a new column with identity & drop the existing column
Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!
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'
Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.
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'
See the following Microsoft SQL Server Forum post for more details:
IDENTITY(1, 1) part with the primary key column –
Ashantiashbaugh In SQL 2005 and above, there's a trick to solve this problem without changing the table's data pages. This is important for large tables where touching every data page can take minutes or hours. The trick also works even if the identity column is a primary key, is part of a clustered or non-clustered index, or other gotchas which can trip up the the simpler "add/remove/rename column" solution.
Here's the trick: you can use SQL Server's ALTER TABLE...SWITCH statement to change the schema of a table without changing the data, meaning you can replace a table with an IDENTITY with an identical table schema, but without an IDENTITY column. The same trick works to add IDENTITY to an existing column.
Normally, ALTER TABLE...SWITCH is used to efficiently replace a full partition in a partitioned table with a new, empty partition. But it can also be used in non-partitioned tables too.
I've used this trick to convert, in under 5 seconds, a column of a 2.5 billion row table from IDENTITY to a non-IDENTITY (in order to run a multi-hour query whose query plan worked better for non-IDENTITY columns), and then restored the IDENTITY setting, again in less than 5 seconds.
Here's a code sample of how it works.
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;
This is obviously more involved than the solutions in other answers, but if your table is large this can be a real life-saver. There are some caveats:
There's a good article on TechNet detailing the requirements above.
UPDATE - Eric Wu had a comment below that adds important info about this solution. Copying it here to make sure it gets more attention:
There's another caveat here that is worth mentioning. Although the new table will happily receive data from the old table, and all the new rows will be inserted following a identity pattern, they will start at 1 and potentially break if the said column is a primary key. Consider running
DBCC CHECKIDENT('<newTableName>')immediately after switching. See msdn.microsoft.com/en-us/library/ms176057.aspx for more info.
If the table is actively being extended with new rows (meaning you don't have much if any downtime between adding IDENTITY and adding new rows, then instead of DBCC CHECKIDENT you'll want to manually set the identity seed value in the new table schema to be larger than the largest existing ID in the table, e.g. IDENTITY (2435457, 1). You might be able to include both the ALTER TABLE...SWITCH and the DBCC CHECKIDENT in a transaction (or not-- haven't tested this) but seems like setting the seed value manually will be easier and safer.
Obviously, if no new rows are being added to the table (or they're only added occasionally, like a daily ETL process) then this race condition won't happen so DBCC CHECKIDENT is fine.
DBCC CHECKIDENT('<newTableName>') immediately after switching. See msdn.microsoft.com/en-us/library/ms176057.aspx for more info. –
Avila id int NOT NULL have to be updated with not null incremental values before SWITCH. Which is not 5 seconds, unfortunately. Otherwise this will be error message: ALTER TABLE SWITCH statement failed because column 'id' does not have the same nullability attribute in tables 'Test' and 'Test2'. –
Cecrops You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.
ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)
ALTER TABLE (yourTable) DROP COLUMN OldColumnName
EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'
Marc
Consider to use SEQUENCE instead of IDENTITY.
In SQL Server 2014 (I don't know about lower versions) you can do this simply, using sequence.
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
From here: Sequence as default value for a column
There is cool solution described here: SQL SERVER – Add or Remove Identity Property on Column
In short edit manually your table in SQL Manager, switch the identity, DO NOT SAVE changes, just show the script which will be created for the changes, copy it and use it later.
It is huge time saver, because it (the script) contains all the foreign keys, indices, etc. related to the table you change. Writting this manually... God forbid.
Simple explanation
Rename the existing column using sp_RENAME
EXEC sp_RENAME 'Table_Name.Existing_ColumnName' , 'New_ColumnName', 'COLUMN'
Example for Rename :
The existing column UserID is renamed as OldUserID
EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'
Then add a new column using alter query to set as primary key and identity value
ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)
Example for Set Primary key
The new created column name is UserID
ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
then Drop the Renamed Column
ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName
Example for Drop renamed column
ALTER TABLE Users DROP COLUMN OldUserID
Now we've adding a primarykey and identity to the existing column on the table.
I'm a java developer that happened to get on a team without a DBA and one where as a developer, I can't get DBA rights. I was tasked with moving an entire schema between two databases, so without having a DBA, I had to do it and do it by running scripts, not being able to use the GUI in SQL Server 2008 because I didn't have admin privileges.
Everything was moved without issue, however, when running a stored procedure on the new schema.table, I found I lost the identity field in a table. I double checked the script that created the table and it was there, however, SQL Server didn't get it when I ran the script. I was told later by a DBA that he had seen this same problem before.
In any event, for SQL Server 2008, these are the steps I took to get this resolved and they worked, so I'm posting this here in the hopes it will be a help to someone. This is what I did as I had FK dependencies on another table that made this more difficult:
I used this query to verify the identity was indeed missing and to view dependencies on the table.
1.) Find statistics on a table:
exec sp_help 'dbo.table_name_old';
2.) Create a duplicate, identical new table, except add an identity field on the PK field where it had been before.
3.) Disable the identity to move data.
SET IDENTITY_INSERT dbo.table_name ON
4.) Transfer the data.
INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT
field1, field2, etc...
FROM
dbo.table_name_old;
5.) Verify the data is there.
SELECT * FROM dbo.table_name_new
6.) Re-enable the identity.
SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF
7.) This is the best script I found to get all the FK relationships to verify which table(s) the original table references as dependencies and I came across many, so it is a keeper!
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.) Make sure you have all the PK and FK scripts for all the tables involved, before this next step.
9.) You can right-click on each key and script this using SQL Server 2008
10.) Drop the FK(s) from the dependency table(s) using this syntax:
ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]
11.) Drop the original table:
DROP TABLE dbo.table_name_old;
13.) These next steps rely on the scripts you created in SQL Server 2008 in step 9.
--Add the PK to the new table.
--Add the FK to the new table.
--Add the FK's back to the dependency table.
14.) Verify everything is correct and complete. I used the GUI to look at the tables.
15.) Rename the new table to the original tables name.
exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';
Finally, everything worked!
As I understood in normal cases we are creating a table with Primary key which is having Identity property
So Rename or Delete a column which is associated with Primary Key constraint will not be possible because constraint Rules are validating column structure.
Tto achieve this we have to process some steps in the following way:
Let us assume TableName = 'Employee' and ColumnName = 'EmployeeId'
1. Add new column 'EmployeeId_new' in the 'Employee' table
ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1)
Now remove column 'EmployeeId' from 'Employee' table
ALTER TABLE Employee DROP COLUMN EmployeeId
This will throw error because of Primary Key Constraint rules are applicable and validating column structure.
*###
'Msg 5074, Level 16, State 1, Line 1 The object [PK_dbo.Employee] is dependent on colmn [EmployeeId].'###
So we have to remove the Primary Key constraint first from the table 'Employee' then we can remove the column
ALTER TABLE Employee DROP constraint [PK_dbo.Employee]
Now we can remove the column 'EmployeeId' from 'Employee' table as did in the previous step where we got error
ALTER TABLE Employee DROP COLUMN EmployeeId
Now Column 'EmployeeId' removed from table
So we will Rename the newly added new column 'EmployeeId_new' with 'EmployeeId'
sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'
To rearrange the table in the same form as It was, we have to add Primary Key Constraint for the column 'EmployeeId'
ALTER TABLE Employee add constraint [PK_dbo.Employee] primary key (EmployeeId)
8. Now the table 'Employee' with 'EmployeeId' is modified for Identity rules along with existing primary key constraint
you can't do it like that, you need to add another column, drop the original column and rename the new column or or create a new table, copy the data in and drop the old table followed by renaming the new table to the old table
if you use SSMS and set the identity property to ON in the designer here is what SQL Server does behind the scenes. So if you have a table named [user] this is what happens if you make UserID and identity
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
Having said that there is a way to hack the system table to accomplish it by setting the bitwise value but that is not supported and I wouldn't do it
If you happen to be using Visual Studio 2017+
This will do it all for you.
By design there is no simple way to turn on or turn off the identity feature for an existing column. The only clean way to do this is to create a new column and make it an identity column or create a new table and migrate your data.
If we use SQL Server Management Studio to get rid of the identity value on column "id", a new temporary table is created, the data is moved to the temporary table, the old table is dropped and the new table is renamed.
Use Management Studio to make the change and then right click in the designer and select "Generate Change Script".
You will see that this is what SQL server in doing in the background.
There isn't one, sadly; the IDENTITY property belongs to the table rather than the column.
The easier way is to do it in the GUI, but if this isn't an option, you can go the long way around of copying the data, dropping the column, re-adding it with identity, and putting the data back.
See here for a blow-by-blow account.
If the original poster was actually wanting to set an existing column to be a PRIMARY KEY for the table and actually did not need the column to be an IDENTITY column (two different things) then this can be done via t-SQL with:
ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])
Note the parenthesis around the column name after the PRIMARY KEY option.
Although this post is old and I am making an assumption about the requestors need, I felt this additional information could be helpful to users encountering this thread as I believe the conversation could lead one to believe that an existing column can not be set to be a primary key without adding it as a new column first which would be incorrect.
Right click on table name in Object Explorer. You will get some options. Click on 'Design'. A new tab will be opened for this table. You can add Identity constraint here in 'Column Properties'.
To modify the identity properties for a column:
That's it, and it worked for me
In my case there were a lot of tables without identity so copying and recreating were going to take too long. I found an easier way to do it if you are using SQL Server Management Studio:
The table is not dropped and the data inside the table and the dependencies remains the same.
I don't believe you can alter an existing column to be an identity column using tsql. However, you can do it through the Enterprise Manager design view.
Alternatively you could create a new row as the identity column, drop the old column, then rename your new column.
ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY CLUSTERED
As per my current condition, I follow this approach. I want to give identity to a primary table after data inserted via script.
As I want to append identity, so it always start from 1 to End of record count that I want.
--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
This will create the same primary key column with identity
I used this links : https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/
Basically there are four logical steps.
Create a new Identity column. Turn on Insert Identity for this new column.
Insert the data from the source column (the column you wished to convert to Identity) to this new column.
Turn off the Insert Identity for the new column.
Drop your source column & rename the new column to the name of the source column.
There may be some more complexities like working across multiple servers etc.
Please refer the following article for the steps (using ssms & T-sql). These steps are intended for beginners with less grip on T-SQL.
generates a script for all tables with primary key = bigint which do not have an identity set; this will return a list of generated scripts with each table;
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;
This answer is very similar to This Answer, with little differences.
For example you have 3 tables like this, with two foreign key
DDL:
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
If you want add identity to id column in Table_1 with dbo schema, and want to start from 10000 with 1 increment, run this query:
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)
Note
If you want copy triggers or extended property of the table too, check this answer and add @SQL2 and @SQL3 to @SQLCreateTable before executing it
© 2022 - 2024 — McMap. All rights reserved.