52 lines
1.1 KiB
Transact-SQL
52 lines
1.1 KiB
Transact-SQL
/*
|
|
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;
|