writting a script to add PKs constraints on columns in many tables at once

Question

writting a script to add PKs constraints on columns in many tables at once

Linda Gomez

Grasshopper

Points: 22
More actions
September 20, 2007 at 5:33 pm

#120496

Hello there,
I need help fast. I'm new to SQL server. I'm on a new project where i'm being asked to create a script to add PK constraints in many table at once. I found online a script to add PKs but how to do it all at once in a single script? I need your help. Thx

Viewing 5 posts - 1 through 4 (of 4 total)

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

ianT SSCrazy Points: 2956 More actions · Answer 1

this is quick & dirty. It assumes that you know the statements are OK and will work cleanly. Build a table holding all your SQL, then run. TEST IT FIRST on a dev database - you will probably find issues creating the individual PKs.

/*

Create a table to hold all of your statements -

create table work_to_do (sqlrequest varchar(500));

--insert the table commands into worktodo - either use

insert into work_to_do (sqlrequest) values ('ALTER TABLE table_1 ADD CONSTRAINT pk_col PRIMARY KEY NONCLUSTERED (Groupid)')

--or type them directly in

*/

DECLARE curWorkToDo CURSOR FOR SELECT * FROM work_to_do;

Declare

@sqlrequest nvarchar(500)

OPEN curWorkToDo;

FETCH NEXT FROM curWorkToDo INTO @sqlrequest;

WHILE

@@FETCH_STATUS = 0

BEGIN;

EXEC sp_executesql @stmt = @sqlrequest

FETCH NEXT FROM curWorkToDo INTO @sqlrequest;

END

;

CLOSE curWorkToDo;

DEALLOCATE

curWorkToDo;

Bob Fazio SSChampion Points: 10794 More actions · Answer 2

I am assuming that you are asking to do nothing more than add

You can loop through all the table names.

SELECT

'alter table ' + NAME + ' add ID INT IDENTITY PRIMARY KEY'

FROM

sys.tables

The output can then be edited.

FYI - THIS IS A VERY LAZY WAY TO DO THIS and WRONG!!!!!! Database's should be thought out and designed. They are not cookie cutter's. This will give you what you asked for.... But be careful what you ask for.

Linda Gomez Grasshopper Points: 22 More actions · Answer 3

Thank you very much Bob. Thank you Grasshoper both answers are very helpfuland I think I have the solution to my problem now. Thx again

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 4

Can you post the code for your final solution, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)