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 > 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 AWBuildVersionfor each database?
- What to do if a database has no
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