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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
bender007Author Commented:
should read "identity specification"
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
BrandonGalderisiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.