?
Solved

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

Posted on 2003-03-12
9
Medium Priority
?
545 Views
Last Modified: 2011-09-20
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.

0
Comment
Question by:Howie_Ly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8124721
You will have to add a new Identity column to the table and then delete the old column.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8124727
I should have added that this is esentially what Enterprise Manager does.

Anthony
0
 
LVL 4

Author Comment

by:Howie_Ly
ID: 8124819
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 4

Author Comment

by:Howie_Ly
ID: 8124880
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
 
LVL 23

Expert Comment

by:adathelad
ID: 8126453
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
 
LVL 2

Expert Comment

by:tony_o1
ID: 8143763
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
 
LVL 4

Author Comment

by:Howie_Ly
ID: 8149661
> 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
 
LVL 2

Expert Comment

by:tony_o1
ID: 8152988



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
 
LVL 2

Accepted Solution

by:
tony_o1 earned 200 total points
ID: 8153144



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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question