Find column across all SQL Server databases

FindColumnInDatabase.sql

/* Find column in all databases */

DECLARE @db_name varchar(100),
		@col_name varchar(100),
		@sql_statement nvarchar(MAX)

-- column you are looking for
SET @col_name = 'PLANNED_SAMPLE_ID'

-- fill cursor with database names
DECLARE db_cursor CURSOR
FOR 
	-- select all DB names into cursor
	SELECT	name 
	FROM	master.sys.databases;
	
OPEN db_cursor

-- get next DB name
FETCH NEXT FROM db_cursor INTO @db_name;

-- loop until we error out
WHILE @@FETCH_STATUS = 0
BEGIN
	/*
	BUILD SQL STATEMENT :
	- USE current DB_NAME from cursor
	- SELECT table names into #TMP TABLE VARIABLE
	- Check #TMP for rows
	- SHOW ROWS IF #TMP CONTAINS DATA
	- DROP TABLE #TMP
	*/
	SET @sql_statement =	'USE ' + @db_name + ';' +

							'SELECT	''' + @db_name + ''' AS [database], ' +
							'		SCHEMA_NAME(schema_id) AS [schema],  ' +
							'		t.name AS table_name, c.name AS column_name ' + 
							'INTO #tmp ' +
							'FROM	sys.tables AS t ' + 
							'INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID ' + 
							'WHERE	c.name = '''+@col_name+''' ' + 							
							'ORDER BY [database], [schema], t.name ; ' +

							'IF EXISTS( SELECT * FROM #tmp ) '+
							'BEGIN ' +
							'	SELECT * FROM #tmp; ' +
							'	DROP TABLE #tmp;' +
							'END;'

-- LIKE column search
-- 'WHERE	c.name LIKE ''%'+ @col_name +'%'' ' + 

-- EXACT column search
-- 'WHERE	c.name = '''+ @col_name +''' ' + 

	-- fire the SQL
	EXEC sp_executesql @sql_statement

	-- Get the next db name
    FETCH NEXT FROM db_cursor INTO @db_name;

END

CLOSE db_cursor;

DEALLOCATE db_cursor;

Thanks Eric. I just ran into a problem where I needed to verify missing columns across several databases on the same server. Your script worked perfectly. Much appreciated.