1

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
2
  • 2
    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"?
    – Nic
    Commented Feb 15, 2017 at 16:41
  • @Nic, Thanks for that! You're right and that is what I am looking for. I updated the question.
    – Jmaurier
    Commented Feb 15, 2017 at 16:54

2 Answers 2

4

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;
0
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
2
  • 1
    have a +1 for effort; and good job with the cursor options.
    – Hannah Vernon
    Commented Feb 15, 2017 at 22:18
  • 1
    I'd just be careful with Sys vs. sys - case can bite you (or future readers). Commented Feb 15, 2017 at 23:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.