Query multiple DB's on same server for Dynamics GP

[trisoldee]

Hello,

I'm new to the forum and would like to start by thanking everyone here for all of your contributions to the community at large.

I started a new job recently using Microsoft Dynamics GP, I'm also using MS SQL 2014. I generally consider myself amateur at best with regards to my SQL query writing abilities. I can usually hold my own when writing queries, I like to say I know enough to be dangerous.

Within GP, my company has over 50 individual companies set up. When looking in SQL, I see one server with all of the companies set up as individual DB's within that single server. My understanding is that the table structures are all identical, is there an "easy" way to run a simple select query off of the entire server? I was shown something that looks like it's doing that but it's calling each individual DB one at a time.

Thank you all in advance for any help.
Tristan K​

 

[trisoldee]

Update,

As I poke around in SQL, I've pulled a list of all the DB names using


SELECT name FROM master.dbo.sysdatabases


Also, here is an example of the query calling each individual DB.


SELECT b.actnumst, a.actdescr, c.year1, c.periodid, c.perdblnc, c.crdtamnt, c.debitamt, c.debitamt - c.crdtamnt 'NetChng', 'ABT' AS 'Source'
FROM ABT.dbo.GL00100 a INNER JOIN
ABT.dbo.GL00105 b ON a.actindx = b.actindx INNER JOIN
ABT.dbo.GL10111 c ON b.actindx = c.actindx
UNION
SELECT b.actnumst, a.actdescr, c.year1, c.periodid, c.perdblnc, c.crdtamnt, c.debitamt, c.debitamt - c.crdtamnt 'NetChng', 'ABT' AS 'Source'
FROM ABT.dbo.GL00100 a INNER JOIN
ABT.dbo.GL00105 b ON a.actindx = b.actindx INNER JOIN
ABT.dbo.GL10110 c ON b.actindx = c.actindx
UNION
SELECT b.actnumst, a.actdescr, c.year1, c.periodid, c.perdblnc, c.crdtamnt, c.debitamt, c.debitamt - c.crdtamnt 'NetChng', 'AAA' AS 'Source'
FROM AAA.dbo.GL00100 a INNER JOIN
AAA.dbo.GL00105 b ON a.actindx = b.actindx INNER JOIN
AAA.dbo.GL10111 c ON b.actindx = c.actindx
UNION
SELECT b.actnumst, a.actdescr, c.year1, c.periodid, c.perdblnc, c.crdtamnt, c.debitamt, c.debitamt - c.crdtamnt 'NetChng', 'AAA' AS 'Source'
FROM AAA.dbo.GL00100 a INNER JOIN
AAA.dbo.GL00105 b ON a.actindx = b.actindx INNER JOIN
AAA.dbo.GL10110 c ON b.actindx = c.actindx
UNION
SELECT b.actnumst, a.actdescr, c.year1, c.periodid, c.perdblnc, c.crdtamnt, c.debitamt, c.debitamt - c.crdtamnt 'NetChng', 'FW' AS 'Source'
FROM FW.dbo.GL00100 a INNER JOIN
FW.dbo.GL00105 b ON a.actindx = b.actindx INNER JOIN
FW.dbo.GL10111 c ON b.actindx = c.actindx
UNION
SELECT b.actnumst, a.actdescr, c.year1, c.periodid, c.perdblnc, c.crdtamnt, c.debitamt, c.debitamt - c.crdtamnt 'NetChng', 'FW' AS 'Source'
FROM FW.dbo.GL00100 a INNER JOIN
FW.dbo.GL00105 b ON a.actindx = b.actindx INNER JOIN
FW.dbo.GL10110 c ON b.actindx = c.actindx
ORDER BY Source


Thank you again.