Skip to content
Advertisement

Remove Identity Setting from all Tables which have Identity

Using SQL Server, to remove the is identity setting (auto increment) for a table, I have to drop and recreate a table.

Is there a way to disable the Idendity Setting for all tables with a simple script?

Advertisement

Answer

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:

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

Query is tested under SQL Server 2016 and it works fine

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement