convert int to int auto-increment in all tables within a database

probably a simple one for you guys.

But say I have 100 tables in my database.
Each table has a int column called "ID".  ID has the properties:

Int
Identity = No

I want them to be:

Int
Identity = Yes
Identity Seed = 1
Identity Increment = 1

Now I don't want to go changed every single one in the database manually.. (ie. in EM : right click on table name -> design -> change the properties and save)  I want to be able to fire a script off or something which will find all the tables in the database with the column "ID" and change their properties.

LVL 4
Howie_LyAsked:
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.

Anthony PerkinsCommented:
You will have to add a new Identity column to the table and then delete the old column.

Anthony
0
Anthony PerkinsCommented:
I should have added that this is esentially what Enterprise Manager does.

Anthony
0
Howie_LyAuthor Commented:
thanks for your comments.. but doesn't SQL Server execute a modify command which leaves data there (if not truncated version of).  If EM does in fact do as you say, wouldn't it do a Add column.. copy contents from old column.. then delete column?  I'm not sure about EM's internal mechanisms.

But that's beside the point.. i've got nearly 100 tables to change and i need a script which will modify "ID" accordingly for EVERY table not just a tblSingleTableName.


0
Ultimate Tool Kit for Technology Solution Provider

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

Howie_LyAuthor Commented:
thanks for your comments.. but doesn't SQL Server execute a modify command which leaves data there (if not truncated version of).  If EM does in fact do as you say, wouldn't it do a Add column.. copy contents from old column.. then delete column?  I'm not sure about EM's internal mechanisms.

But that's beside the point.. i've got nearly 100 tables to change and i need a script which will modify "ID" accordingly for EVERY table not just a tblSingleTableName.


0
adatheladCommented:
Do you need to keep the current values in the ID column?

As Anthony said, you cannot alter an existing column via T-SQL to make it an IDENTITY column. This can only be done using the Enterprise Manager GUI. To do it programmatically, you must remove the existing column, then create a new IDENTITY column.
0
tony_o1Commented:
adathelad is correct about not being able to alter table to create an Identity colomn, but there is sort of a workaround,

you can create a cursor using the sysobjects table to handle all of the tables in your database then

with each table
add a new Identity column

set identity insert to the table on

update the sql identity value to the Orig ID value(this can get tricky if 'ID' ranges overlap, if they do set the identity seed higher than the highest value that is currently in your id fields when you create the column)

drop the original ID colomn

then rename the New ID column to 'ID'

you can then do a CHECKIDENT ('table_name', RESEED)
to reset the identity columns to use the next available autonumber

then loop

email me if you need help on this..... i have a script that does this..... It was a case of scripting a database gone bad

Hope this helps,

Tony

0
Howie_LyAuthor Commented:
> email me if you need help on this..... i have a script that does this.....

Would you be able to post your script up for me to look at?  thanks.
0
tony_o1Commented:



Declare @Tablename varchar(50) -- place holder
Declare @str as varchar(1000) -- string to hold sql command

 declare cur cursor for

--make sure this is not a system coloumn name
SELECT     sysobjects.name
FROM         sysobjects INNER JOIN
                      syscolumns ON sysobjects.id = syscolumns.id
WHERE     (syscolumns.name = N'pkid') -- 'pkid is the name of the column your looking for
ORDER BY sysobjects.name
open cur
fetch cur into @tablename
while (@@fetch_Status = 0)
Begin
     set @str = 'select * into tmpTable from ' + @tablename
     exec (@str)

     set @str = 'delete from ' + @tablename
     exec (@str)

     set @str = 'alter table ' + @tablename + 'drop column pkid '
     exec (@str)

     set @str = 'alter table ' + @tablename + ' add column pkid [int] indentity(1,1) not null'
     exec (@str)

     set @str = 'set indentity_insert  ' + @tablename + ' on '
     exec(@str)

     set @str = 'INSERT INTO ' + @tablename + ' SELECT * from tmpTable '
     exec (@str)

     set @str = 'set indentity_insert  ' + @tablename + ' off '
     exec(@str)

     drop table tmpTable


        fetch cur into @tablename

end

close cur

deallocate cur


0
tony_o1Commented:



Declare @Tablename varchar(50) -- place holder
Declare @str as varchar(1000) -- string to hold sql command

 declare cur cursor for

--make sure this is not a system coloumn name
SELECT     sysobjects.name
FROM         sysobjects INNER JOIN
                      syscolumns ON sysobjects.id = syscolumns.id
WHERE     (syscolumns.name = N'pkid') -- 'pkid is the name of the column your looking for
ORDER BY sysobjects.name
open cur
fetch cur into @tablename
while (@@fetch_Status = 0)
Begin
     set @str = 'select * into tmpTable from ' + @tablename
     exec (@str)

     set @str = 'delete from ' + @tablename
     exec (@str)

     set @str = 'alter table ' + @tablename + 'drop column pkid '
     exec (@str)

     set @str = 'alter table ' + @tablename + ' add column pkid [int] indentity(1,1) not null'
     exec (@str)

     set @str = 'set indentity_insert  ' + @tablename + ' on '
     exec(@str)

     set @str = 'INSERT INTO ' + @tablename + ' SELECT * from tmpTable '
     exec (@str)

     set @str = 'set indentity_insert  ' + @tablename + ' off '
     exec(@str)

     drop table tmpTable


        fetch cur into @tablename

end

close cur

deallocate cur


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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.