Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SunnyDee
Yak Posting Veteran
79 Posts |
 Posted - 2011-04-04 : 15:26:54
|
| Hello All:I've inherited a project where there are multiple DBs on SQL Server. The total no. of DBs is over 20, so a union is not a good solution.I can get the required dbs from sys.databases, but I'm stuck on how to have one query select from the list of dbs. If I select the following into a temp table, how do I then query the temp table with my actual query using the name or database_id?----Temp table query results from ----------------------Select * from sys.databasesWHERE NAME NOT IN ('MASTER', 'TEMPDB', 'MODEL', 'MSDB' )AND (NAME NOT LIKE '%REPORTSERVER%')Any help is appreciated.Thanks, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SunnyDee
Yak Posting Veteran
79 Posts |
Posted - 2011-04-05 : 11:50:18
|
| I thought union would not be a good solution because of performance reasons.. Could I use a union with a dynamic database? If so, could you please provide a code example?Thanks, |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-05 : 12:15:27
|
quote:
Could I use a union with a dynamic database?
Not unless you use dynamic SQL.I tend to use sp_MSforeachdb whenever I have to traverse multiple databases. It's a little tricky to use but not much worse than normal dynamic SQL. Here's an example:exec sp_msforeachdb 'if db_id(''?'')<=4 OR ''?'' LIKE ''%REPORTSERVER%'' return; -- this ignores system db's and ReportServerSELECT ''?'' AS DatabaseName, * FROM [?].dbo.myTable WHERE ID=100The ? is a substitution character, and sp_msforeachdb runs a cursor over each database and replaces the ? with the name, then executes the statement.If you provide more detail on what you're trying to do I can take a stab at an sp_msforeachdb solution. |
|
|
|
|
|
|
|
|
One Query Across Multiple DBs