Single query to get data from multiple tables in one database

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.

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

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.

StackExchange Database Administrators Q#167752, answer score: 5