Changing Identity Specification in all tables

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

Select allOpen in new window

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.

should read "identity specification"

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

Thanks,

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

Did u copy the result from the result pane and run those results on a new query window

>you can only enable identity_insert for one table at a time.

Yup, I agree

I just opened each table in design mode and disabled the identity increment option.

>>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."

Oh ok , Thanks for that info. I appreciate it.