querying and updating multiple databases?

[ChrisH2]

Not sure this is the correct place to be asking this but here goes.

Our DBA who is also project leader designed our project (Warehouse dist. package) so that each company has its own database. At the application level the user has a menu option to change to another company. All companies contain the exact same table, stored proc, triggers etc.

I have been given a task to create a program that will allow us to run TSQL code against all companies at once (multi databases). Instead of using query analyser and selecting that database from the drop down list and individually executing the code against each database.
The object is to speed up design time and to eliminate missing a company, the program will also be used on site when the package is finished to control upgrades.

To start with I was trying to query all databases to produce a list of only our databases, eliminating master and any other databases that someone might create.

select [name] from master..sysdatabases
gives me the all the databases. How can I join sysobjects to check for the existence of a table to see is the database is one of ours.

Has anyone tried executing scripts against multiple databases at once?

Is this multi-database design a bad idea?
I think it would of been better to add the company column to each table in the database but I think we are stuck with the design now.

Any thoughts?

Thanks in advance

Chris

 

[gmmastros]

You can use this script to find all databases on the server that contain a table with a given name.

Create Table #Temp (DatabaseName VarChar(255))

exec sp_msforeachdb 'Insert Into #Temp(DatabaseName) 
                     Select Table_Catalog 
                     from [?].Information_Schema.Tables 
                     Where Table_Name=''[!]TableNameToFind[/!]'''

Select * From #Temp
Drop Table #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

It depends. Sometimes using multiple DB is better that one HUGE DB with company ID in ALL tables.

Here query you want, keep in mind it is not very well tested:

SELECT * from master..sysdatabases Tbl1
INNER JOIN 
(SELECT Name, OBJECT_ID(Name+'..YourTableNameHere') AS Test FROM master..sysdatabases) Tbl2
ON Tbl1.name = Tbl2.Name AND Tbl2.Test IS NOT NULL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ChrisH2]

Thanks a lot for all your help guys.
I got it working with

SELECT [Name] FROM master..sysdatabases
WHERE OBJECT_ID([Name]+'..myfile') IS NOT NULL