Identity for primary keys

Question

Identity for primary keys

ramu.guggilla

SSC Eights!

Points: 829
More actions
July 18, 2008 at 12:42 am

#122568

Hi folks,
I am using the MS SQL Server 2005. In that my Database having aroung 150 tables. I need to add the identity for all the primary key columns. is there any script to add the identity for the primary key columns?
i can add the identity through the SSMS BUT it will take time to add the identity for each table. i need a scipt to add the identity.
thanks,
Ramu

Viewing 7 posts - 1 through 6 (of 6 total)

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

Mike John SSCertifiable Points: 7216 More actions · Answer 1

You could certainly develop a script that would do this, but I would ask why, making every primary key an identity is not always a good thing. Certainly in some situations an identity makes a useful surrogate key, but this should be thought through an dthe kost appropriate (for the business problem) column or columns be defined as the primary key. Then, and only then ,if there are no appropriate candidate keys, or you are optimising things to reduce references natural keys that may change woul;d you introduce the surrohate by means of identity. You would also still of coure want unique or primary key constraints on the natural key if you have one to avoid duplicates.

Mike John

ramu.guggilla SSC Eights! Points: 829 More actions · Answer 2

HI,

I am using the database thru java. I need to insert the rows in to the tables that time i need to use the identity. i can get the max number from the code and increment by 1 and can insert in to database. but for each table if i will get this then the connections will increas. so i need to use the identity for incrementing the vlaues.

i need script to add the identity. give me one example on how to add idetnity for the table.

- Ramu

r.dragoi Right there with Babe Points: 788 More actions · Answer 3

alter table bubu

add fid int identity (1,1) primary key not null

kr,

R

r.dragoi Right there with Babe Points: 788 More actions · Answer 4

and here is another example:

create table bubu (fcol1 int)

create table tutu (fcol1 int)

create table #tmptables (fid int identity (1,1), tblname varchar(50))

insert into #tmptables (tblname)

select 'bubu'

union

select 'tutu'

declare @cnt int,@sqlst varchar(100),@i int,@tbname varchar(20)

set @cnt = (select count(*) from #tmptables)

set @i = 1

while (@i <= @cnt )

begin

select @tbname = tblname from #tmptables where fid = @i

set @sqlst= 'alter table ' + @tbname

+ ' add fid int identity (1,1) '

exec (@sqlst)

set @i = @i + 1

end

drop table #tmptables

cheers,

R

ramu.guggilla SSC Eights! Points: 829 More actions · Answer 5

here you added the new column and added the identity for that column. BUT my question is i already having the table with all the fields. i need to change the data type for the primary key column using the script.

r.dragoi Right there with Babe Points: 788 More actions · Answer 6

Well , in this case it sounds like new tables are needed, lots of work 🙂 .