What is the Best way to

[Kenos]

Hello Everyone


I have over 130 databases
that are exactly the same, (Different Data)

I need to drop a Column in a single table
table = Products
Column = Old_CAT_ID

Is there a way to create a stored PRoc to cycle all databases
and drop just one Column

Thanks

 

[SQLBill]

There's an undocumented Stored Procedure that can help:

Procedure:
sp_MSforeachdb
@command1
@replacechar = '?'
[,@command2]
[,command3]
[,@whereand]
[,@precommand]
[,@postcommand]

Purpose:
execute up to three commands for every database on the system. @replacechar will be replaced with the name of each database. @precommand and @postcommand can be used to direct commands to a single result set.

Example:
EXEC sp_MSforeachdb @command1 = 'Print "Listing ?=', @command2 = 'SELECT * FORM ?', @whereand = ' AND name like "title%"'

-From the Gurus's Guide to Transact-SQL by Ken Henderson

-SQLBill

 

[clapag22]

If all the DB's are on server, you can write this query in Master and then take the results and post into another query window and exec it:

set nocount on

select 'use ' + name + '
GO ' + '
alter table Products drop column Old_CAT_ID
GO'
from sysdatabases

 

[SQLBill]

Using sp_MSforeachdb it would be:

EXEC sp_MSforeachdb @command1 = 'USE ? ALTER TABLE Products DROP COLUMN Old_CAT_ID'

(BTW-In the example in my previous post FORM should have been FROM)

-SQLBill