FindFieldInAllTables.sql

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

SELECT *
INTO #Results
FROM
(
SELECT	sche.name AS [SchemaName],
		tabl.name AS [TableName],
		cols.name  AS [ColumnName]
	
FROM        sys.columns cols
JOIN        sys.tables  tabl   ON cols.object_id = tabl.object_id
JOIN        sys.schemas sche   ON tabl.schema_id = sche.schema_id
WHERE       cols.name LIKE '%SiteID%' 
AND EXISTS (SELECT * FROM        sys.columns c2
JOIN        sys.tables  t2   ON c2.object_id = t2.object_id
JOIN        sys.schemas s2   ON t2.schema_id = s2.schema_id
WHERE       c2.name = 'CompanyID' AND t2.name = tabl.name AND s2.schema_id = sche.schema_id) 
--AND	c.name not IN ('xxx', 'yyy')
) AS x

SELECT * FROM #Results
ORDER BY [TableName], [ColumnName]