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
2009-11-22 19:56:44
+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
2009-11-22 20:18:03
psasik, seraphym wants to add a column named 'delete' to each table, not delete a column from each table.
Aaron Bertrand
2009-11-22 20:21:44
very cool, thanks for your help!
seraphym
2009-11-22 21:29:49
+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
2009-11-22 19:57:44