[thelist] SQL Server ALTER TABLE

Joshua Olson joshua at waetech.com
Wed Feb 18 07:51:52 CST 2004


> -----Original Message-----
> From: Les Lytollis
> Sent: Wednesday, February 18, 2004 4:33 AM
>
> Hi All,
>
> I need to add a column to all tables (except system tables) in a SQL
> Server DB. Is there a way to do:
>
> <pseudocode>
> ALTER TABLE * ADD [newcolumn] varchar (50)
> WHERE [table] NOT [systemtable]
> </pseudocode>

Les,

You will need to create a list of tables (which you can either query from
the master database or use the system sp "sp_tables" or something similar)
and then loop over the list and apply the alteration one at a time.  All of
the looping could be done either through middleware or within a stored
procedure.

For this sort of thing (assuming this is a one-time operation), I generally
suggest creating code that outputs a text script that can be reviewed and
then executed as a batch process (ie, using Query Analyzer) only after
you've confirmed that it's right.

Best of luck,

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168




More information about the thelist mailing list