Changing Identity Specification in all tables

How would one change identity specification in all tables in a DB ? I have a backup DB thats an exact replication of the production, but in order to copy data , Identity specification needs to be disabled.

How do i do that without having to open each table individually in design mode ?
bender007Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BrandonGalderisiConnect With a Mentor Commented:
aneeshattingal...
you can only enable identity_insert for one table at a time.


bender... in order to disable the identity property, you have to add a column with a new name, transfer the values of the identity column to that column, then drop the identity column and rename the newly created column.

Your columns will now be out of order so hopefully all of your inserts specify the column names and if you are selecting it for any ASP code, you are using column names and not the column number.

If you want them in the same order, you will need to create a new table with the columns in the correct order, copy the data over, drop the old table and rename the new to take the same name.
0
 
BrandonGalderisiCommented:
For the copy operation, you could enable identity_insert.
Set identity_insert {TABLE NAME} on
 
insert into {Table Name} (all columns)
select (all columns) from otherserver.dbo.{table name}
 
Set identity_insert {TABLE NAME} off

Open in new window

0
 
bender007Author Commented:
yes I am aware of setting the identity_insert when doing an insert. But i was looking for a query that would permanently disable Identity speculation on all tables in a DB.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
bender007Author Commented:
should read "identity specification"
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
run this, copy the results and run it on another Query window

select 'Set identity_insert '+TABLE_NAME+' on '
from information_schema.tables
WHERE IDENT_CURRENT(TABLE_NAME) IS NOT NULL






0
 
bender007Author Commented:
Thanks,

The query ran fine, but Identity specification and Identity Increment still was not disabled

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Did u copy the result from the result pane and run those results on a new query window
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>you can only enable identity_insert for one table at a time.

Yup, I agree
0
 
bender007Author Commented:
I just opened each table in design mode and disabled the identity increment option.

0
 
Anthony PerkinsCommented:
>>I just opened each table in design mode and disabled the identity increment option.<<
Than you followed BrandonGalderisi (you just don't know it)  EM will basically do the following behind the scenes:
"create a new table with the columns in the correct order, copy the data over, drop the old table and rename the new to take the same name."
0
 
bender007Author Commented:
Oh ok , Thanks for that info. I appreciate it.
0
All Courses

From novice to tech pro — start learning today.