Need to find a column across several databases on a particular server and there were a lot of databases so used this query to build a temp table that holds the found metadata for the particular SQL Server.

Need to construct dynamic sql statement that can then be executed against all databases on the server through the use of the sp_MSForEachDB

Replace the text TABLE_TO_SEARCH_FOR and COLUMN_YOU_WANT_TO_FIND

IF OBJECT_ID('#ColumnDetails', 'U') IS NOT NULL
	DROP TABLE #ColumnDetails

CREATE TABLE #ColumnDetails (
	DBaseName varchar(100),
	TableSchema varchar(50),
	TableName varchar(100),
	ColumnName varchar(100),
	DataType varchar(100),
	CharMaxLength varchar(100)
)
EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #ColumnDetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE Table_Name like ''%TABLE_TO_SEARCH_FOR%'''

SELECT DBaseName
FROM #ColumnDetails
WHERE ColumnName IN ('COLUMN_YOU_WANT_TO_FIND')
GROUP BY DBaseName