Remove All Identity Tables

Question

Remove All Identity Tables

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

vahid.arr Ten Centuries Points: 1063 More actions · Answer 1

Gazareth (10/9/2012)
Lokesh Vij (10/9/2012)
Gazareth (10/9/2012)
Nils Gustav Stråbø (10/9/2012)
Lokesh Vij (10/9/2012)Here you go:
Won't work. Only one table at the time can have IDENTITY_INSERT OFF
Only one table at the time can have IDENTITY_INSERT ON.
I don't think setting it to off for all tables has any effect, as they're already set to that?
You can't remove the identity property from a column - you'll need to drop the column.
Thanks Gazareth for correcting me 🙂
No problem. Think we're all a little confused as to what the OP is trying to do 🙂

Performance is not important Because I want To Do it One Time.I want All Removed Data from My Main Database Come to Another Database So I remove Foreign Key From this Database And I want To remove Also Identity.how can I do That sequentialy????????????plz I Have 80 Table I cant Do That One by one

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

vahid.arr (10/9/2012)
Performance is not important Because I want To Do it One Time.I want All Removed Data from My Main Database Come to Another Database So I remove Foreign Key From this Database And I want To remove Also Identity.how can I do That sequentialy????????????plz I Have 80 Table I cant Do That One by one

Ok, are you copying data between databases (main -> another), or changing the tables directly within the main database?

Removed Foreign Keys from which database - main or another?

The Identity property cannot be removed from an existing column - the column has to be dropped.

If you need to keep the values, either create a new column without Identity property, copy values across, then drop, or create a new table, copy data across, drop original, and rename. In either case, you'll need to handle constraints too.

Thanks

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 3

Gazareth (10/9/2012)
Nils Gustav Stråbø (10/9/2012)
Lokesh Vij (10/9/2012)Here you go:
Won't work. Only one table at the time can have IDENTITY_INSERT OFF
Only one table at the time can have IDENTITY_INSERT ON.

Bad typo from me 🙂

vahid.arr Ten Centuries Points: 1063 More actions · Answer 4

Gazareth (10/9/2012)
vahid.arr (10/9/2012)
Performance is not important Because I want To Do it One Time.I want All Removed Data from My Main Database Come to Another Database So I remove Foreign Key From this Database And I want To remove Also Identity.how can I do That sequentialy????????????plz I Have 80 Table I cant Do That One by one
Ok, are you copying data between databases (main -> another), or changing the tables directly within the main database?
Removed Foreign Keys from which database - main or another?
The Identity property cannot be removed from an existing column - the column has to be dropped.
If you need to keep the values, either create a new column without Identity property, copy values across, then drop, or create a new table, copy data across, drop original, and rename. In either case, you'll need to handle constraints too.
Thanks

I get Script(Righ Click on DB and Task>Generate Scripts..) From Main Database And Exec To Another Database and Make False Script Foreign Key But In generat Script There is no option For Identity.Is there Any Way That I Copy All Of My main Database Tables without Foreign Key And Identity to Another Database?????

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

vahid.arr (10/9/2012)
Gazareth (10/9/2012)
vahid.arr (10/9/2012)
Performance is not important Because I want To Do it One Time.I want All Removed Data from My Main Database Come to Another Database So I remove Foreign Key From this Database And I want To remove Also Identity.how can I do That sequentialy????????????plz I Have 80 Table I cant Do That One by one
Ok, are you copying data between databases (main -> another), or changing the tables directly within the main database?
Removed Foreign Keys from which database - main or another?
The Identity property cannot be removed from an existing column - the column has to be dropped.
If you need to keep the values, either create a new column without Identity property, copy values across, then drop, or create a new table, copy data across, drop original, and rename. In either case, you'll need to handle constraints too.
Thanks
I get Script(Righ Click on DB and Task>Generate Scripts..) From Main Database And Exec To Another Database and Make False Script Foreign Key But In generat Script There is no option For Identity.Is there Any Way That I Copy All Of My main Database Tables without Foreign Key And Identity to Another Database?????

If all your tables have identity seed & increment 1,1, it should be relatively easy - just use the Find/Replace function in Management Studio on the generated script - replace IDENTITY(1,1) with blank.

If you have different seeds/increments, then you'll have to go through the script manually or run a few find/replaces - I'd recommend going though manually anyway just to check.

Even with 80 tables it shouldn't take more than 10 minutes or so.

Cheers