Single query to get data from multiple tables in one database

Question

I have 36 Microsoft SQL Server VM's, each with a specific database ending in _Sec as the DB name. There are 2 tables in this database that I need to get data from in a single query: tblConnGroups and tblConnGroupsDet.

tblConnGroups has 3 columns that I need to include in the results

tblConnGroups.ID
tblConnGroups.GroupName
tblConnGroups.Provisional

tblConnGroupsDet has essentially one column that is needed in the results.

tblConnGroupsDet.GroupID
tblConnGroupsDet.DBName

tblConnGroups.ID is a key used in tblConnGroupsDet.GroupID, however in tblConnGroups the ID field has a one-to-many relationship to the GroupID field in tblConnGroupsDet.

The Connection Group Name only appears in the tblConnGroups with its ID, and other information, but the GroupID will be in each row of the tblConnGroupsDet with a member database name.

USE [Name_Sec];
GO
SELECT ID,Groupname,Provisional 
from [tblConnGroups] 
ORDER BY ID ASC
ID GroupName Provisional
514 Name_FY0910 0
844 Name_FY1011 0
1588 NameTest_FY1516 1
USE [Name_Sec];
GO
SELECT GroupID,DBName 
FROM [tblConnGroupsDet] 
ORDER BY GroupID ASC
GroupID DBNAME
514 Name_Base
514 Name_FY0910
514 Name_State0910
514 Name_portal
514 Name_FY1011

I tried a couple of different join queries to match the tblConnGroups.ID and tblConnGroupsDet.GroupID and haven't quite figured out how to get the information I want out of these two tables.

Basically I need a query that will give me these results where GroupName will get repeated for each DBName where the ID/GroupID match:

GroupName,DBName,FileSystemPath

I'm hopeful that this is clearer than mud, I don't do a whole lot of DB Admin work, we've been trying to find a DBA, and this is a hat I don't put on very often.

Answer 1

SSMS has a feature called Server Groups, which allows exactly that functionality.

enter image description here

As in the above image, add a new Server Group, then add each target SQL Server to the group.

Then right-click the newly created group, and choose "New Query". Enter the following into the query window:

DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + 'USE ' + QUOTENAME(d.name) + ';
SELECT SECDB = ''' + d.name + '''
 , g.*
 , gd.*
FROM dbo.tblConnGroups g
 INNER JOIN dbo.tblConnGroupsDet gd ON g.ID = gd.GroupID
ORDER BY gd.GroupID, gd.DBName;
'
FROM sys.databases d
WHERE d.name LIKE '%_Sec';
EXEC sys.sp_executesql @cmd;

The code above will dynamically execute your query against any/all databases named like *_Sec on all SQL Servers in the group.

Answer 2

USE [Name_Sec];
GO
SELECT tblConnGroupsDet.GroupID, tblConnGroupsDet.DBName, tblConnGrou‌​ps.GroupName,tblConn‌​Groups.ID,tblConnGro‌​ups.Provisional
 FROM tblConnGroupsDet LEFT JOIN tblConnGroups ON tblConnGroupsDet.GroupID=tblConnGroups.ID
 ORDER BY GroupID ASC

If you have a 37th server, you can get results from all the servers using a the 37th as a Central Management Server under SQL Server Management Studio. Basically, this would let you run the same query on all 36 servers at once (so, it only works if the database is [NameSec] on all the servers).

In SSMS, register the 37th server under Central Management Server, and the other 36 under that one. Open a new query with the Central Management Server selected, and run it - it will hit all the other servers.

Note: If you don't have a 37th server, set up one of the 36 as a Central Management Server, and you can run the query on the other 35, and then run on the 36th independently.