Multiple Database Query

[ABerkowicz]

Hi -

I have a table across 150 databases and I want to do a simple select statement and get the results from all companies in one view. I tried a cursor but it doesnt work in the FROM statement. Any ideas on how to be able to select FROM many different databases instead of doing something like this...

select field from Dbase1.dbo.table union all
select field from Dbase2.dbo.table union all
select field from Dbase3.dbo.table

etc. I dont want to have to hard code this and also there is a table that has all of the database ids in it. Thoughts?

 

[ysommer]

Hi,

I would create the query dynamically by using a table variable go over each database id using a while loop and add it to the query. Then execute the query.

Y

 

[SQLSister]

I wopuld suck it up and hard code unless this is something that will only happen on the back end. Dynamic SQl is a very bad thing to use. It is not secure and would require you to allow user permissions at the table level for everyone of these tables opening your database up to possible sql injection or internal fraud. It is also slower that non-dynamic sql.

Read the following link for an understanding of how very bad dynamic SQl is:

http://www.unixwiz.net/techtips/sql-injection.html

"NOTHING is more important in a database than integrity." ESquared