Find a specific table and column value across all databases

Question

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:

enter image description here

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 > 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:

enter image description here

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

1. How to run a select [Database Version] from AWBuildVersion for each database?
2. 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

enter image description here

Answer 1

Comments inline:

-- The table to find
DECLARE @find nvarchar(257) = N'dbo.AWBuildVersion';
-- Holds results
DECLARE @results table 
(
 [Db_Name] sysname PRIMARY KEY,
 [Database Version] nvarchar(25) NOT NULL
);
-- Current database 
DECLARE @db sysname;
-- AdventureWorks databases cursor
DECLARE dbs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT D.[name] 
FROM sys.databases AS D
WHERE 
 D.[name] LIKE N'AdventureWorks%'
 AND D.state_desc = N'ONLINE'
 AND DATABASEPROPERTYEX(D.[name], 'Collation') IS NOT NULL
ORDER BY
 D.[name];
OPEN dbs;
WHILE 1 = 1
BEGIN
 -- Next database
 FETCH dbs INTO @db;
 IF @@FETCH_STATUS = -1 BREAK;
 -- Find [Database Version] if @find table exists
 INSERT @results ([Db_Name], [Database Version])
 EXECUTE sys.sp_executesql
 N'
 -- Construct 3-part object name
 DECLARE @object nvarchar(386) = 
 QUOTENAME(@db) + N''.'' +
 ISNULL(QUOTENAME(PARSENAME(@find, 2)), '''') + N''.'' +
 QUOTENAME(PARSENAME(@find, 1));
 -- Query to find [Database Version]
 DECLARE @sql nvarchar(max) = N''
 SELECT 
 Db_Name = '''''' + @db + '''''',
 [Database Version] 
 FROM '' + @object;
 IF OBJECT_ID(@object, N''U'') IS NOT NULL
 EXECUTE (@sql)',
 N'@db sysname, @find nvarchar(257)',
 @db = @db, @find = @find;
END;
CLOSE dbs; DEALLOCATE dbs;
SELECT
 R.[Db_Name],
 [Table_Name] = @find,
 R.[Database Version]
FROM @results AS R
ORDER BY
 R.[Db_Name];

Answer 2

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

result of the script

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.

• Francesco Mantovani: Thank you, it worked but the results are not formatted like in your picture. Do you know how I can format the result in the same 3 columns |Server|Database|DatabaseVersion|
• SQLStad: I'm not sure why you would get a different result. I executed the script on my machines and was able to get the same result as in the picture. I did remove a line of code that generated an error. Can you send me a printscreen of what the result is on your machine?
• Francesco Mantovani: they get formatted differently if you point it against just one server. Interesting. The code is brilliant and it works, I still have a few problem connecting to a remote server because I'm not administrator but I think I can sort it out
• Francesco Mantovani: Connecting to local-host is fine, connecting to remote servers is still a pain WARNING: [Invoke-DbaSqlQuery][17:43:26] Failure | Exception calling "Open" with "0" argument(s): "Login failed for user..... I added -SqlCredential (Get-Credential sa) so it prompts me the login, i enter the password but then it gives me again the same error because is trying to login with MYDOMAIN\username instead of sa\password

Answer 3

I'd probably use sp_foreachDB contained in Brent Ozar's First Responder Kit:

EXEC dbo.sp_foreachDB @command1 = 'select [Database Version] from dbo.AWBuildVersion', 
 @name_pattern = 'AdventureWorks%'

Haven't tested this but I believe the syntax should work.

• Francesco Mantovani: tested, is not working: Msg 208, Level 16, State 1, Line 1 Invalid object name 'AWBuildVersion'.
• LowlyDBA - John M: I don't have all those AW DBs handy right now..this is some troubleshooting I believe you can take on yourself.