By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,435 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

Deleting multiple columns from multiple objects

P: n/a
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.

Apr 1 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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_SCHEMA.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
Apr 2 '08 #2

P: n/a
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 2 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.