views:

128

answers:

2

I need to add a delete flag column to all 40 user tables in a database. I could write a script to loop through sys.tables, but I thought I'd check and see if anyone has either a better solution, or pre-created sql for this scenario.

+9  A: 

There is an undocumented but well known stored procedure sp_msforeachtable:

exec sp_msforeachtable 'alter table ? add flag bit not null default 0';
Remus Rusanu
+1 but...seraphym is asking for a way to remove a column. Though your statement could easily be rewritten to drop, the name of the target column would have to be uniform across all the tables.
Paul Sasik
psasik, seraphym wants to add a column named 'delete' to each table, not delete a column from each table.
Aaron Bertrand
very cool, thanks for your help!
seraphym
+3  A: 

No, it's a manual loop.

Or you could build up a single SQL statement of course...

SELECT 
    'ALTER TABLE ' + T.name + ' ADD foo int NULL'
FROM
    sys.tables AS T
WHERE
    T.is_ms_shipped = 0

Or the undocumented

EXEC sys.sp_MSforeachtable 'ALTER TABLE ? ADD foo int NULL'
gbn