給定一個我只能讀取主資料庫的資料庫服務器，我需要掃描服務器上的所有資料庫以查找名稱中包含“SMTP 郵件設定”的表。我還需要知道每個表中“SMTP 服務器”欄位的值。
我已經能夠拼湊以下內容，其中列出了存盤我需要的資料的資料庫和表名稱。
EXEC sp_MSforeachdb 'USE [?] SELECT TABLE_CATALOG as DB_Name, Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''%SMTP Mail Setup%'''
我現在被困住了，因為我似乎無法弄清楚如何從給定的表中提取“SMTP 服務器”欄位。有沒有更好的方法來解決這個問題？
uj5u.com熱心網友回復：
您將需要根據第一個查詢的結果生成并執行動態 SQL。
嘗試以下（有點過度設計）代碼：
DECLARE @TableNamePattern sysname = '%SMTP Mail Setup%'
DECLARE @ColumnName sysname = 'SMTP Server'
IF OBJECT_ID('TempDb..#SelectedTables') IS NOT NULL DROP TABLE #SelectedTables
CREATE TABLE #SelectedTables (DB_Name sysname, Table_Name sysname)
DECLARE @SqlTemplate1 VARCHAR(MAX) = '
USE [?]
INSERT #SelectedTables
SELECT T.TABLE_CATALOG as DB_Name, T.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON C.TABLE_CATALOG = T.TABLE_CATALOG
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE T.TABLE_TYPE = ''BASE TABLE''
AND T.TABLE_NAME LIKE <TableNamePattern>
AND C.COLUMN_NAME = <ColumnName>
'
DECLARE @Sql1 VARCHAR(MAX) =
REPLACE(REPLACE(
@SqlTemplate1
, '<TableNamePattern>', QUOTENAME(@TableNamePattern, ''''))
, '<ColumnName>', QUOTENAME(@ColumnName, ''''))
EXEC sp_MSforeachdb @Sql1
SELECT * FROM #SelectedTables ORDER BY DB_Name, Table_Name
DECLARE @SqlTemplate2 VARCHAR(MAX) = 'UNION ALL
SELECT <DB_NAME_Text> AS DB_NAME, <Table_Name_Text> AS Table_Name, <Column_Name>
FROM <DB_NAME>..<Table_Name>
'
DECLARE @Sql2 VARCHAR(MAX) = STUFF((
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@SqlTemplate2
, '<DB_NAME_Text>', QUOTENAME(T.DB_NAME, ''''))
, '<Table_Name_Text>', QUOTENAME(T.Table_Name, ''''))
, '<DB_NAME>', QUOTENAME(T.DB_NAME))
, '<Table_Name>', QUOTENAME(T.Table_Name))
, '<Column_Name>', QUOTENAME(@ColumnName))
FROM #SelectedTables T
ORDER BY T.DB_NAME, T.Table_Name
FOR XML PATH(''),TYPE
).value('text()[1]','nvarchar(max)')
, 1, 9, '') -- Remove initial UNION ALL
SET @Sql2 = @Sql2 '
ORDER BY 1, 2, 3' -- Lazy way of referencing columns
PRINT @Sql2 -- Might be truncated
EXEC (@Sql2)
DROP TABLE #SelectedTables
我添加了檢查以確保在所選表中定義了該列，并且該表是一個真正的表（'BASE TABLE'）而不是一個視圖。sql 模板通過一系列替換函式運行，這些函式插入正確參考和轉義的物件名稱。第一個模板是原始執行的 sql 的擴展版本。第二個用于為每個表生成一個系列或選擇。
將FOR XML PATH(''),TYPE所有生成的選擇連接到一個 XML 字串中，.value()最后可靠地提取該文本，避免任何 XML 編碼工件。較新的 SQL Server 版本支持STRING_AGG()可以替代使用的函式，但我手頭的代碼已經在使用 FOR XML。
每個查詢都以這樣的方式開始，UNION ALL以便所有結果都顯示在一個組合網格中。STUFF(..., 1, 11, '')剝離前導UNION ALL。
最后生成的 SQL 被列印并執行。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/514562.html
標籤：sql服务器tsql
上一篇：使用列對齊將T-SQL查詢結果復制并粘貼到SSMS文本編輯器中
下一篇：從快照表中按日期選擇最新的新行