In a kind of random post about a few separate things I'm going to show how i used SQL Developer and a couple of tricks to run a report against multiple SQL Server databases (and by database i kind of mean schemas in Oracle terms, though SQL Server has that term too..... just think of them as schemas...)
So anyway we have a SQL Server instance hosting HPQC (That's HP Quality Centre - a testing tool), this instance contains a number of project databases (schemas.....) all with the same tables in each one. The data contained is relevant for that project only but the structure is the same. It could have been structured i guess as just one database/schema with all the data in but HP chose not to do that and the whole schema is replicated per project - there are pros and cons to either approach i guess.
The requirement is to generate a very basic summary report that shows a count for all the test runs there have been in each project in the past year.
The query to generate this against an individual database is this:
select count(*) from run where (run.RN_EXECUTION_DATE >= '2014-01-01' and run.RN_EXECUTION_DATE <= '2014-12-31')
So i need to find an easy way to do that across 86 databases.......
The first problem i had was actually getting a SQL client to be able to connect to the instance in the first place to run the query. The normal SQL Management client was not installed on any servers that had firewall access to the database, and with my account i didn't have admin rights to be able to install one. The database server itself is supported by a third party so no joy there - so what could i use...?
This is where SQL Developer came in useful - did you know you can simple install another jdbc driver and connect to SQL Server from it? Well you can (unfortunately i did this ages ago and can't remember how i did it - but i remember it being very easy).
So anyway once this extra driver is installed you will see this change in the connectivity screen of SQL developer
You also get sybase as well at the same time - neat huh?
OK so now i have the tool i can log on using the usual port/hostname etc etc
SO now i'm in i just need to figure out how to work this magic query. After many years working with Oracle i often use the trick - use SQL to create SQL - and the exact same principle applies here too - i just need to build a SQL statement that will create the SQL i actually want to run - in a kind of pseudo-dynamic way (big words i know....)
Anyway it's much simpler than that makes it sound....
First up i need a way of getting a list of all the databases in the system - a quick google reveals that this will do the trick
select name from master..sysdatabases
Now i have that i need to use it as a way of generating a select statement against each database in the list - now i won't bore you with the trial and error in between - i'll just show you the finished result:
SELECT 'select ' + CHAR(39) + name + CHAR(39) +' ,count(*) from ' + name + '.td.run where (run.RN_EXECUTION_DATE >= '+CHAR(39)+'2014-01-01'+CHAR(39)+' and run.RN_EXECUTION_DATE <= '+CHAR(39)+'2014-12-31'+CHAR(39)+') union all'
FROM master..sysdatabases
where name not in ('master','model','msdb','qcsiteadmin_dbV11','tempdb','LiteSpeedLocal')
Looks a little odd - but all this will do is generate 86 rows - each of which is a select statement. The useful bits to note here are the use of CHAR(39) (instead of CHR(39) in Oracle....) to generate a ' character in the output, the use of + to concatenate strong together (as opposed to || in oracle) and the inclusion of union all at the end of every line.
The first few lines of output then look like this
select 'DB1' ,count(*) from DB1.td.run where (run.RN_EXECUTION_DATE >= '2013-01-01' and run.RN_EXECUTION_DATE <= '2013-12-31') union all
select 'DB2' ,count(*) from DB2.td.run where (run.RN_EXECUTION_DATE >= '2013-01-01' and run.RN_EXECUTION_DATE <= '2013-12-31') union all
select 'DB3' ,count(*) from DB3.td.run where (run.RN_EXECUTION_DATE >= '2013-01-01' and run.RN_EXECUTION_DATE <= '2013-12-31') union all
etc
etc
up to line 86
So i think you can see where I'm going with this - the output is in it's own right a select statement now - all i have to do is remove the final union all at the end of the last line and then run the 86 line statement (it takes a while as it has to be run in every database) - and then gives me this result
| DB1 | 0 | ||
| DB2 | 236 | ||
| DB3 | 15 | ||
| etc |
So there we have it a quick way to summarize data across lots of SQL Server databases using an Oracle tool and tricks learned from an Oracle database...
Comments
Post a Comment