Do you want to DROP COLUMN on all these tables? There is no
SQL statement that "deletes a column." (DELETE deletes rows). If
you want to DROP COLUMN on all of them, here is a script that
will create all the statements to do that - you can review them to be
sure that your column doesn't appear in a table where it should be
left, then copy what you need of the output up to the query analyzer
window and run it.
If there are constraints on the column you are deleting, it may not
go so smoothly - and I would be very cautious making widespread
changes to hundreds of tables in any case.
By the way, if you want to do something else with the tables, like
UPDATE T SET doomedColumn = NULL
on all of them, you can just change the definition of sql in my
code. I could have had this script actually drop all the columns,
by using exec() instead of print, but I don't want to be responsible
for that suggestion!
SET NOCOUNT ON
--Put your column name here
SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.TABLES.TABLE_NAME
ON TABLE_TYPE = 'BASE TABLE'
DECLARE C CURSOR FOR
SELECT sql FROM DropColQrys
DROP TABLE DropColQrys
> short answer, no.
> a delete statement can only affect one table per instruction. so, you would
> need to cursor through all of your tables and delete from one by one.
> by the way, if you don't need the data in the column and you no longer use
> it in your query, why not just leave it there. other than taking *extra*
> space there is really no harm.
> if the column is truly no longer needed, you ought to just remove/drop it.
> > Hi,
> > I have a common column exists in all my tables and now I don't need this
> > column any more. Is there a way to mess delete the column in all tables,
> > instead of openning each individual table and delete it. I have about 500
> > tables.
> > Thanks for the advise.