Deleting multiple columns from multiple objects

[doverj]

Is there a way to delete from multiple tables/views a column with a specific
name? For example, a database has 50 tables and 25 views all have a column
named ColumnA. Is it possible to write a simple script that will delete
every column named ColumnA from the database?

Seems to be it would be possible and I can somewhat vision it using
sysobjects but without wanting to spend too much time generating the script
(when I could in shorter time manually delete) thought I'd pose the question.

Thanks.

[Philipp Post]

Re: Deleting multiple columns from multiple objects

Hi,

for the tables you could create a script using a cursor and do some
dynamic sql in it with ALTER TABLE ... DROP COLUMN ... getting table
names from sys.tables or INFORMATION_SCH EMA.TABLES. Regarding views I
see no proper way of automating it.

However developing and testing of that will most likely take as much
time as doing it manually (using a script window and simply replace
the table name) for 50 tables. Further scripting delete actions for
your database objects can be dangerous. If you make a mistake in it
you quickly loose a lot of things which you did not want to...

brgds

Philipp Post

[Erland Sommarskog]

Re: Deleting multiple columns from multiple objects

doverj (u42617@uwe) writes:

Is there a way to delete from multiple tables/views a column with a
specific name? For example, a database has 50 tables and 25 views all
have a column named ColumnA. Is it possible to write a simple script
that will delete every column named ColumnA from the database?
>
Seems to be it would be possible and I can somewhat vision it using
sysobjects but without wanting to spend too much time generating the
script (when I could in shorter time manually delete) thought I'd pose
the question.

SELECT 'ALTER TABLE ' + o.name + ' DROP COLUMN nisse'
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
WHERE o.type = 'U'
AND c.name = 'nisse'

For the views, I'm afraid manual editing is the only option. Hm, I think
Red Gate has a refactoring tool, but I have not looked into it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx