Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2003-03-12
9
Medium Priority
?
546 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
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

580 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