/*
Server:		HCIMON
Format:		HCI 
Business:	OTHER
type:		PROD
Version:	No Arizona db on this instance

01.11.2022, TSC
*/
USE master;

DECLARE @dbs TABLE([name] VARCHAR(100));
DECLARE @results TABLE([schema] VARCHAR(100), [table] VARCHAR(100), [db_name] VARCHAR(100));
DECLARE @keyword VARCHAR(100) = 'batch';

DECLARE @query VARCHAR(MAX);
DECLARE @tpl VARCHAR(MAX)=' USE @db@;

SELECT DISTINCT c.TABLE_SCHEMA, c.TABLE_NAME, DB_NAME()
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME LIKE ''%@keyword@%'' ';

INSERT INTO @dbs([name])
SELECT db.name
FROM sys.databases db
WHERE db.database_id > 4;

/* declare variables */
DECLARE @db_name VARCHAR(MAX);

DECLARE scan_db CURSOR FAST_FORWARD READ_ONLY FOR 
	SELECT [name] FROM @dbs d

OPEN scan_db

FETCH NEXT FROM scan_db INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @query = REPLACE(REPLACE(@tpl,'@keyword@', @keyword), '@db@', @db_name);
	INSERT INTO @results ([schema], [table], [db_name])
	EXEC(@query);

    FETCH NEXT FROM scan_db INTO @db_name
END

CLOSE scan_db
DEALLOCATE scan_db

SELECT * 
FROM @results r;