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