在所有數據庫中查找特定的表和列值

這是一個關於邏輯的問題，可以幫助我更多地了解如何迭代到我的數據庫中。

在我的伺服器上，我有幾個 AdventureWorks* 數據庫：

我有一個冷查詢幫助我遍歷我的所有數據庫 AdventureWorks* 而不是：

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

對於每個 AdventureWorks* 數據庫，我想AWBuildVersion在每個 DB 旁邊列出：

此時我很困惑，因為有兩個問題我不知道如何解決：

1. 如何select [Database Version] from AWBuildVersion為每個數據庫執行一個？
2. 如果數據庫沒有AWBuildVersion表怎麼辦？

在這裡我找到了這個查詢：

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'

這使我接近我的目標，但它顯示了AWBuildVersion我需要列 Database Version = 11.0.2100.60的表名

內聯評論：

-- 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];

引用自：https://dba.stackexchange.com/questions/199961