目前我的游標結果產生一個@database_name VARCHAR，我試圖弄清楚如何將它作為一個變數，我可以用它來回圈查詢多個資料庫。我能找到的與游標相關的大部分內容與我擁有的回圈和列印非常相似。
我經歷了很多不同的方法，這可能離我最好的嘗試還很遠，我開始倒退
DECLARE
@cursor_db CURSOR
DECLARE
@database_id VARCHAR(10),
@database_name VARCHAR(255);
SET @cursor_db = CURSOR FOR
SELECT database_id, name
FROM sys.databases
WHERE name LIKE 'Company%';
OPEN @cursor_db;
FETCH NEXT FROM @cursor_db INTO @database_id, @database_name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @database_id ' ' @database_name
SELECT *
FROM @database_name i
WHERE sys.columns i LIKE '%Orders%'
FETCH NEXT FROM @cursor_db INTO @database_id, @database_name;
END;
CLOSE @cursor_db;
DEALLOCATE @cursor_db;
uj5u.com熱心網友回復：
嘗試這個：
DECLARE @DBNamePattern varchar(50) = 'Company%'
, @ColNamePattern varchar(50) = 'Order%'
;
SELECT
DatabaseName = C.Table_Catalog
, DatabaseID = D.database_id
, SchemaName = C.TABLE_SCHEMA
, TableName = C.Table_Name
, ColName = C.Column_Name
FROM INFORMATION_SCHEMA.COLUMNS C
JOIN sys.databases D ON D.Name = C.TABLE_CATALOG
WHERE
Table_Catalog LIKE @DBNamePattern
AND Column_Name LIKE @ColNamePattern
ORDER BY
DatabaseName
, TableName
uj5u.com熱心網友回復：
如果有人想知道結果，您可以在動態查詢中使用連接字串：
-- before cursor opens
DECLARE @base_query1 VARCHAR(max);
DECLARE @base_query2 VARCHAR(max);
DECLARE @query VARCHAR(max)
SET @base_query1 = 'SELECT COUNT(*) AS CNT FROM '
SET @base_query2 = '..TableName' -- expected table name
-- inside the loop
SET @query = CONCAT( @base_query1, @database_name, @base_query2 )
EXEC(@query);
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/362302.html
標籤：sql-server 查询语句