Search schema catalog for column names (for each database template)

search-column-name-all-dbs.sql

-------------------------------------------------------------------------------
-- search schema catalog for column names
-- example template showing how to execute for each database
-------------------------------------------------------------------------------
DECLARE	@Databases			TABLE (DatabaseName sysname primary key);
DECLARE	@DatabaseName		sysname;
DECLARE @sqlstmt_replaced	nvarchar(max);

-- temp table to hold merged results instead of multiple results sets
DROP TABLE IF EXISTS #tmp;
CREATE TABLE #tmp (
	DatabaseName	sysname		NOT NULL
,	SchemaName		sysname		NOT NULL
,	TableName		sysname		NOT NULL
,	ColumnName		sysname		NOT NULL
PRIMARY KEY (DatabaseName, SchemaName, TableName, ColumnName)
);

DECLARE @sqlstmt_template	nvarchar(max) =
N'
USE [?];

INSERT	#tmp
SELECT	C1.TABLE_CATALOG, C1.TABLE_SCHEMA, C1.TABLE_NAME, C1.COLUMN_NAME
FROM	INFORMATION_SCHEMA.COLUMNS AS C1
WHERE	C1.TABLE_CATALOG = DB_NAME()
  AND	C1.COLUMN_NAME LIKE ''%Address%''
  AND	EXISTS (
			SELECT	*
			FROM	INFORMATION_SCHEMA.TABLES AS T1
			WHERE	T1.TABLE_TYPE = ''BASE TABLE''
			  AND	T1.TABLE_CATALOG = C1.TABLE_CATALOG
			  AND	T1.TABLE_SCHEMA = C1.TABLE_SCHEMA
			  AND	T1.TABLE_NAME = C1.TABLE_NAME
		);
';

-- filter databases effectively when dealing with thousands of them
INSERT	@Databases
SELECT [name]
FROM	sys.databases
WHERE	is_auto_close_on = 0
  --AND	state_desc IN (N'ONLINE', 'EMERGENCY')
  AND	[name] LIKE N'Adventure%';

-- first iteration
SET		@DatabaseName = (SELECT MIN(DatabaseName) FROM @Databases);

WHILE @DatabaseName IS NOT NULL
BEGIN
	-- better to always wait between executions to be able to cancel the query...
	WAITFOR DELAY '00:00:00.300';
	-- execute for each database
	SET	@sqlstmt_replaced = REPLACE(@sqlstmt_template, N'?', @DatabaseName);
	BEGIN TRY
		EXEC (@sqlstmt_replaced);
	END TRY
	BEGIN CATCH
		IF ERROR_NUMBER() = 942 -- database offline (may have changed state meanwhile)
		BEGIN
			DECLARE	@ErrorMessage nvarchar(max) = FORMATMESSAGE('Msg %d, Level %d, State %d, Line %d - %s', ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE());
			PRINT	@ErrorMessage
			DELETE @Databases WHERE DatabaseName = @DatabaseName;
			SET @DatabaseName = (SELECT MIN(DatabaseName) FROM @Databases);
			CONTINUE;
		END
		ELSE
			THROW;
	END CATCH
	-- next iteration
	DELETE	@Databases WHERE DatabaseName = @DatabaseName;
	SET		@DatabaseName = (SELECT MIN(DatabaseName) FROM @Databases);
END

-- show merged results
SELECT * FROM #tmp;