March 15, 2009 at 10:34 am
I have several databases which contain a set of common tables. I need to build some queries that combine the records, from all the databases, into a common result set. I'm trying to sort out a way to specify which database to execute a statement on. Then I could loop thru the databases and join the results with UNION ALL. The USE statement does not seem to accept variables.
Any thoughts?
March 15, 2009 at 11:07 am
You can build and execute dynamic SQL within your loop. I use this approach to build qualified table names for databases that reside on linked servers. We pull summary data periodically from them, and sometimes our DBAs move databases from one server to another to balance the load. Using dynamic SQL, they just have to update the table that drives the loop and the code doesn't have to change.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 15, 2009 at 11:13 am
Thanks for the reply.
I was just starting to look at that approach. I think it may resolve my issue. This is a development server where new databases get created quite often. I'm trying to build some cross-database queries for management reporting. I don't want to have to recreate them everytime a new development DB is added.
March 15, 2009 at 11:20 am
It should work well for you.
Do you have some sort of convention for distinguishing those databases which should be queried from others which shouldn't? You can ensure good performance by creating a stored procedure that exists in each database, and then just trying to execute that stored procedure. If you get an error that the stored procedure isn't found, just move on to the next database. This also allows you to alter the query within the procedure to handle non-standard databases. We have a couple of databases with additional tables, and the query has to be slightly different to produce meaningful statistics.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 15, 2009 at 11:48 am
Only a few people are allowed to create databases. Provided they follow the established naming conventions (common suffix) I should have no problem selecting the databases from sys.databases.
I want to avoid having to add anything to the individual databases. These are development copies of customer databases and I don't want anything in them that should not go to a customer. These databases do not get very large (< 5 GB) and the tables I would be accessing would have a maximum of a few thousand records. I don't expect performance issues.
March 15, 2009 at 11:53 am
Sounds like you're good to go then. I was just throwing out ideas about a few "gotchas" I ran across the first time around. Looks like we're done here, so I'm going to unsubscribe. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 16, 2009 at 8:32 pm
I know this is a 2005 forum, but you should look into policy-based management for SQL 2008- this would allow you to set up criteria and only allow objects with certain naming conventions, etc. You can also disallow people to create the objects unless they meet certain criteria.
For 2005, you could also accomplish that with a DDL trigger. I use an SSIS package to deploy the DDL trigger to each database. To do so, I use a for-each loop within the package to loop through a list of databases, and then dynamically update the connection string for each database, then run an execute sql task against the database itself. If you'd like a sample of that, let me know. I don't have it on my home pc here.
Just thought I'd throw some additional ideas out there.
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply