Primary Key

Question

Primary Key

VastSQL

SSC-Dedicated

Points: 36121
More actions
March 23, 2009 at 10:17 pm

#94744

Hi Experts,
I have more than 100 tables in a database and is there any way to create primary key to all tables in a go.Please help me as I am new to SQL Server Development.
TIA

Viewing 13 posts - 1 through 12 (of 12 total)

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

I doubt there's a way to do this in a single go. Each table will need and individual PK created. Unless you've named the primary key in all 100 tables ID or something (a horrible idea), you can't easily generate code to create it. However, if you're intent on trying, try using a combination of sp_msforeachtable and sp_executesql to generate ad hoc queries.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

rknairassyst Right there with Babe Points: 729 More actions · Answer 2

rknairassyst

Right there with Babe

Points: 729

March 24, 2009 at 10:27 pm

#965779

.

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 3

Thanks Grant.

My idea was to create a procedure like

create procedure rkn_pk

declare @tabname char(50)

declare @colname char(50)

as

alter table @tabname add constraint @tabname+pk primary key on @colname

Is that possible.

TIA

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 4

Created this but getting error while executing as the constraint name is creating lot of space

alter procedure rkn_pk

@tabname char(50),

@colname char(50)

as

begin

declare @sql as varchar(500)

select @sql='alter table '+ @tabname +'add constraint '+ @tabname+'_pk1'+' primary key'+ '('+@colname+')'

print(@sql)

end

Please help

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 5

VastSQL

SSC-Dedicated

Points: 36121

March 25, 2009 at 4:59 am

#965887

Solved

sandhyarao49 SSCrazy Points: 2215 More actions · Answer 6

Hi Ratheesh,

Can you please give me the code that solved your issue.

Iam also looking for the same query and conditions.

Thanks,

Sandhya.

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 7

ALTER procedure [dbo].[rkn_pk]

@tabname char(50),

@colname char(50)

as

begin

declare @sql as varchar(500)

select @sql='alter table '+ RTRIM(@tabname) +' add constraint '+ RTRIM(@tabname)+'_pk1' + ' primary key'+ '('+RTRIM(@colname)+')'

execute (@sql)

end

EXEC [rkn_pk] 'RKN','NO'

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

Hi

I would advice to use QUOTENAME to avoid SQL injections:

ALTER PROCEDURE [dbo].[rkn_pk]

@tabname CHAR(50),

@colname CHAR(50)

AS

BEGIN

SELECT @tabname = LTRIM(RTRIM(@tabname)), @colname = LTRIM(RTRIM(@colname))

DECLARE @pk_name AS VARCHAR(100)

SELECT @pk_name = @tabname + '_pk1'

DECLARE @sql AS VARCHAR(500)

SELECT @sql = 'ALTER TABLE '+ QUOTENAME(@tabname) + ' ADD CONSTRAINT ' + QUOTENAME(@pk_name) + ' PRIMARY KEY ' + ' ('+ QUOTENAME(@colname) + ')'

EXECUTE (@sql)

END

Two other questions:

Why do you use CHAR instead of VARCHAR?

Why do you not script your PRIMARY KEY as CLUSTERED?

Greets

Flo

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 9

Thanks Florian for the Tips,

Actually created this as an experiment. I want to convert this to DB2 which i dont know..Do you have any idea? I will be very thankful if you can help me in doing that.

TIA

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 10

Hi Ratheesh

Sorry, I have absolutely no idea about DB2.

Good luck

Flo

J-440512 SSCertifiable Points: 6285 More actions · Answer 11

By default, a column declared as a PRIMARY KEY should be clustered. Looking at the script I did not see anything about dropping an existing clustered index prior to creating the PRIMARY KEY.

In addition to adding the PRIMARY KEY (a serious design error) you should also take a good like at any index present in the database as well.

Regards

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 12

J (3/28/2009)
By default, a column declared as a PRIMARY KEY should be clustered. Looking at the script I did not see anything about dropping an existing clustered index prior to creating the PRIMARY KEY.

Did you notice my question below my posted script? 😉

I just didn't add the CLUSTERED option because the previous script did not contain it.

Greets

Flo