Find a specific table and column value across all databases This is a question about logic that could help me understand more how to iterate into my DBs. 

On my server I have several AdventureWorks* databases:



I have a query that cold help me iterate through all my databases, AdventureWorks* and not:

CREATE TABLE #LIst_DB (name nvarchar(128))

INSERT INTO #LIst_DB 

select name from  sys.databases
WHERE database_id &gt; 4
AND state = 0;

select * from #LIst_DB

DROP TABLE #LIst_DB


For each AdventureWorks* database I want to list AWBuildVersion next to each DB:



At this point I'm puzzled because there are 2 problems I don't know how to solve:


How to run a select [Database Version] from AWBuildVersion for each database? 
What to do if a database has no AWBuildVersion table?


Here I found this query:

DECLARE @SQL NVARCHAR(max)

SET @SQL = stuff((
            SELECT '
UNION
SELECT ' + quotename(NAME, '''') + ' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name
FROM ' + quotename(NAME) + '.sys.tables WHERE NAME =  @TableName '
            FROM sys.databases
            ORDER BY NAME
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 8, '')

--PRINT @SQL;

EXECUTE sp_executeSQL @SQL
    ,N'@TableName varchar(30)'
    ,@TableName = 'AWBuildVersion'


That brings me close to my goal but it shows me the table name AWBuildVersion while I need the column Database Version = 11.0.2100.60
I would not want to do that with T-SQL because it limits me in the amount of servers I could get the results from.

Instead I would create a PowerShell script like this:

$servers = @( 'server1', 'server2')

$query = "SELECT [Database Version] AS DatabaseVersion FROM [dbo].[AWBuildVersion];"

$results = @()
$comment = @()

# Loop through the servers
foreach ($server in $servers) {
    $databases = Get-DbaDatabase -SqlInstance $server | Where-Object {$_.Name -like 'AdventureWorks*'}
    Write-Host "Going through server $server"

    # Loop through the databases
    foreach ($database in $databases) {
        # Reset variables
        $result = @()
        $data = $null
        $comment = $null

        if ($database.Tables.Name -contains 'AWBuildVersion') {
            Write-Host "- Querying database $database"

            $data = Invoke-DbaSqlQuery -SqlInstance $server -Database $database.Name -Query $query 
        }
        else {
            $comment += 'Could not find table AWBuildVersion'
        }

        $result = [PSCustomObject]@{
            Server          = $server
            Database        = $database.Name
            DatabaseVersion = $data.DatabaseVersion
            Comment         = $comment -join ', '
        }

        $results += $result
    }
}

$results




You do need the dbatools powershell module to run it.

What it does is:


Iterates through the servers you assign. 
Retrieves the databases that have 'AdventureWorks' in the name.
Iterates through the databases and executes the query.
If it cannot find the table it adds a comment to the results that the table could not be found.