Remove Identity from a column in a table
You cannot remove an IDENTITY specification once set.
To remove the entire column:
ALTER TABLE yourTable
DROP COLUMN yourCOlumn;
Information about ALTER TABLE here
If you need to keep the data, but remove the IDENTITY column, you will need to:
- Create a new column
- Transfer the data from the existing
IDENTITYcolumn to the new column - Drop the existing
IDENTITYcolumn. - Rename the new column to the original column name
How to remove identity on primary key column in SQL Server 2016
Create a new column, copy the data, drop original column
alter table yourtable
add newcolumn int
update yourtable
set newcolumn=oldcolumn
alter table yourtable
drop column oldcolumn
Remove identity flag from a column in PostgreSQL
I don't think there is a safe and supported way to do that (without catalog modifications). Fortunately, there is nothing special about sequences that would make dropping them a problem. So take a short down time and:
remove the default value that uses the identity sequence
record the current value of the sequence
drop the table
create a new sequence with an appropriate
STARTvalueuse the new sequence to set new default values
If you want an identity column, you should define it on the partitioned table, not on one of the partitions.
How to drop IDENTITY property of column in SQL Server 2005
IF you are just processing rows as you describe, wouldn't it be better to just select the top N primary key values into a temp table like:
CREATE TABLE #KeysToProcess
(
TempID int not null primary key identity(1,1)
,YourKey1 int not null
,YourKey2 int not null
)
INSERT INTO #KeysToProcess (YourKey1,YourKey2)
SELECT TOP n YourKey1,YourKey2 FROM MyTable
The keys should not change very often (I hope) but other columns can with no harm to doing it this way.
get the @@ROWCOUNT of the insert and you can do a easy loop on TempID where it will be from 1 to @@ROWCOUNT
and/or
just join #KeysToProcess to your MyKeys table and be on your way, with no need to duplicate all the data.
This runs fine on my SQL Server 2005, where MyTable.MyKey is an identity column.
-- Create empty temp table
SELECT *
INTO #TmpMikeMike
FROM (SELECT
m1.*
FROM MyTable m1
LEFT OUTER JOIN MyTable m2 ON m1.MyKey=m2.MyKey
WHERE 1=0
) dt
INSERT INTO #TmpMike
SELECT TOP 1 * FROM MyTable
SELECT * from #TmpMike
EDIT
THIS WORKS, with no errors...
-- Create empty temp table
SELECT *
INTO #Tmp_MyTable
FROM (SELECT
m1.*
FROM MyTable m1
LEFT OUTER JOIN MyTable m2 ON m1.KeyValue=m2.KeyValue
WHERE 1=0
) dt
...
WHILE ...
BEGIN
...
INSERT INTO #Tmp_MyTable
SELECT TOP (@n) *
FROM MyTable
...
END
however, what is your real problem? Why do you need to loop while inserting "*" into this temp table? You may be able to shift strategy and come up with a much better algorithm overall.
Remove Identity Setting from all Tables which have Identity
You cannot remove an IDENTITY specification once set.
I assume that you are using SQL SERVER:
You have to use dynamic SQL query to drop all Identity Columns:
DECLARE @SQL as varchar(8000)
SET @SQL = ''
select @SQL = @SQL + 'ALTER TABLE [' + [TABLE_SCHEMA] + '].[' + [TABLE_NAME] + '] DROP COLUMN [' + [COLUMN_NAME] + '] ; '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY TABLE_NAME
EXEC( @SQL)
Note: COLUMNPROPERTY is starting with SQL SERVER 2008
UPDATE1 :
According to your comment the following query
- loop over Tables
- Check if table contains Identity column.
- If True, It Check if table has Relations
- If False, it check if it contains Data
- If not containing Data it drop table and recreate it without Identity.
Note: Query contains a lot of code because generating create table script
DECLARE @strSchema as varchar(50)
DECLARE @strName as varchar(50)
DECLARE @strQuery as varchar(8000)
DECLARE csrCursor Cursor For SELECT [TABLE_SCHEMA],[TABLE_NAME] FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY TABLE_NAME
OPEN csrCursor
FETCH NEXT FROM csrCursor INTO @strSchema,@strName
WHILE @@FETCH_STATUS <> -1
BEGIN
IF NOT EXISTS(SELECT
name, OBJECT_NAME(parent_object_id) 'Table'
FROM
sys.foreign_keys
WHERE
referenced_object_id = OBJECT_ID('[' + @strSchema + '].[' + @strName + ']'))
BEGIN
SET @strQuery = 'IF NOT EXISTS(SELECT * FROM [' + @strSchema + '].[' + @strName + '])
BEGIN
DROP TABLE [' + @strSchema + '].[' + @strName + '];
'
DECLARE @table_name SYSNAME
SELECT @table_name = @strSchema + '.' + @strName
DECLARE
@object_name SYSNAME
, @object_id INT
SELECT
@object_name = '[' + s.name + '].[' + o.name + ']'
, @object_id = o.[object_id]
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + '.' + o.name = @table_name
AND o.[type] = 'U'
AND o.is_ms_shipped = 0
DECLARE @SQL NVARCHAR(MAX) = ''
;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 @SQL = 'CREATE TABLE ' + @object_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 = 'decimal'
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.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
CASE WHEN dc.[definition] IS NOT NULL 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 ' + @object_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 ' + @object_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
+ ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_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
AND i.[type] = 2
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
), '')
SET @strQuery = @strQuery + @SQL + '
END'
--PRINT(@strQuery)
EXEC (@strQuery)
END
FETCH NEXT FROM csrCursor INTO @strSchema,@strName
END
Close csrCursor
DEALLOCATE csrCursor
Query is tested under SQL Server 2016 and it works fine
Can you remove Identity from a primary key with Entity Framework 6?
You can not use ALTER COLUMN to set whether a column is an identity column (How to alter column to identity(1,1)).
Instead, you have to:
- (backup DB)
CREATETMP_table with columns of original table, but ID column set to identity: falseSET IDENTITY_INSERT [TMP_Table] ON- copy data from original to TMP table
SET IDENTITY_INSERT [TMP_Table] OFFDROPoriginal table- Rename TMP_table to original table name (
EXECUTE sp_rename)
Tip: change the column in SQL Management Studio and inspect the emitted script (SQL SERVER – Add or Remove Identity Property on Column).
How to delete identity property from primary key column through sql query in sql server 2005
Unfortunately there is no other way of dropping an IDENTITY property from a table without creating a dropping a recreating the table first. So you would need to store the data in a new table, drop the original table, and then rename the interim table to the correct name.
OR
You could just add a new column to your table, then copy your data into this new column, drop your identity column from the table and finally set your new column as a primary key.
How to find your IDENTITY columns...
select object_name(object_id) table_name,name column_name
from sys.columns
where is_identity = 1
Related Topics
Select Rows Which Are Not Present in Other Table
Optimize Group by Query to Retrieve Latest Row Per User
How to Select the First Row of Each Group
Difference Between Union and Union All
Insert Text With Single Quotes in Postgresql
How to Do an Update Statement With Join in SQL Server
Null Values Inside Not in Clause
Is There Any Rule of Thumb to Construct SQL Query from a Human-Readable Description
You Can't Specify Target Table For Update in from Clause
SQL Update from One Table to Another Based on a Id Match
What Is the Meaning of the Prefix N in T-SQL Statements and When Should I Use It
Error in MySQL When Setting Default Value For Date or Datetime
How to Use an Insert Statement'S Output Clause to Get the Identity Value
Create a Date from Day Month and Year With T-Sql