List column value for all databases that have a specific table and column

Question

What I am really looking for is the name of each database with table ApplicationVersion and column [Version] as well as the value in [Version]. Some of my databases don't have the specified table column and should be ignored. The table always has only one row.

Database ApplicationVersion
DB_001 5.123.0.234
DB_002 5.123.0.234
DB_003 6.223.0.435

• Nic: Databases don't have columns, tables have columns. Is that what you are looking for, and also to check the value of every column in every database for the string "ApplicationVersion"?
• Jmaurier: @Nic, Thanks for that! You're right and that is what I am looking for. I updated the question.

Answer 1

I really, really, really recommend against sp_MSforeachdb. It's easy, sure, but it is undocumented, unsupported, and buggy as all get out.

Here is a dynamic SQL solution:

CREATE TABLE #x([Database] sysname, ApplicationVersion varchar(32));
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'
 BEGIN TRY
 INSERT #x EXEC ' + QUOTENAME(name) +N'.sys.sp_executesql 
 N''SELECT DB_NAME(), MAX(Version) FROM dbo.ApplicationVersion'';
 END TRY
 BEGIN CATCH
 PRINT CHAR(32);
 END CATCH;'
FROM sys.databases 
WHERE state = 0; -- database is online
EXEC sys.sp_executesql @sql;
SELECT [Database], ApplicationVersion FROM #x;
DROP TABLE #x;

Answer 2

This is the garbage that I came up with while Aaron was killing it. Same result with much less style.

declare database_cursor cursor 
local static read_only forward_only
for select name from sys.Databases;
create table #ApplicationVersion
(
 DatabaseName varchar(128),
 [Version] varchar(128)
)
declare @DatabaseName varchar(127);
open database_cursor
fetch next from database_cursor into @DatabaseName
while @@FETCH_STATUS = 0
begin
exec (
 'use ' + @DatabaseName +
 'declare @count int;
 set @count = (select count(name) from sys.tables where name = N''ApplicationVersion'')
 if (@count) > 0
 begin
 insert into #ApplicationVersion select ''' + @DatabaseName + ''', [Version] from ApplicationVersion
 end'
 )
 fetch next from database_cursor into @DatabaseName
end
close database_cursor
deallocate database_cursor
select * from #ApplicationVersion
drop table #ApplicationVersion

• Hannah Vernon: have a +1 for effort; and good job with the cursor options.
• Aaron Bertrand: I'd just be careful with Sys vs. sys - case can bite you (or future readers).