SQL 2005: How to add same column and FK across multiple tables.

Question

Hello

I need to add a Column "CurrentLifeCycleId" [int] into 73 tables and also add a Foreign Key from this new column to another single table "LifeCycle" Id column...is there a simple way I can do this in a few statements rather than go through each table one by one.

The column does not need to start off being NULL as I will delete all records from these tables before I start.

It's the weekend and I don't want to spend all today doing this :)

Thanks in advance.

Answer 1

Use SQL to built a script:

SELECT N'ALTER TABLE ' +quotename(name)+N' ADD COLUMN CurrentLifeCycleId (int) null;
alter table '+quotename(name)+N' ADD CONSTRAINT fk_LifeCycleId 
   (CurrentLifeCycleId ) references other_table (LifeCycleId);
'
FROM sys.tables where name in ('table1', 'table2', ..., 'table73');

Press Ctrl-T to push have results as text, execute in SSMS, select the result and paste it into a new query window. There are fabcier ways (like PowerShell), but this is fairly easy and straight forward. If the tables ar ein differnet schema, make sure you add that too into the script output.

Also make sure the script is correct before running it :)