Posted by Jeff Kish on 06/05/07 22:08

Hi.

I've read up on this, and have something that works, but I was wondering if
there is anything I'm overlooking with this.

Situation is:

I have a bunch of tables.. I need to modify table2 as part of an upgrade of a
database schema.

I am using T-SQL scripts to do the trick which I'm writing myself.

I need to add a new varchar(8) column that is not null to the primary key.
I have a default I would like to use for the initial ddl modification.
I want to get rid of the default after the modification is complete, but leave
the column not null for future operations.
..

(Some if the code I'm using I took from one of Erlands posts.. hope I'm not
abusing it).
Here is the code I'm using now.. it basically adds the column 'institution_id'
as not null along with a default.
Then I jump through a couple of hoops trying to get rid of the default.
Finally I setup the primary key again.

I can only feel I'm supposed to be maybe using a constraint column with a name
to do this easier/more properly.

set @dynamicsql = ' alter table institution_xref add institution_id
varchar(60) not null default ''' + @default_institution_id + ''' '
EXEC (@dynamicsql)
set @dynamicsql = ' alter table institution_xref alter column
institution_id varchar(60) not null '
EXEC (@dynamicsql)
select @institution_iddefault = object_name(cdefault) from syscolumns
where id = object_id('institution_xref') and name = 'institution_id'
exec(' alter table institution_xref drop constraint ' +
@institution_iddefault)
set @dynamicsql = ' alter table institution_xref drop constraint
institution_xref_pk '
EXEC (@dynamicsql)
set @dynamicsql = ' alter table institution_xref with nocheck add
constraint institution_xref_pk primary key clustered (originalcode,
institution_id) '
EXEC (@dynamicsql)

thanks
Jeff
Jeff Kish

[Back to original message]