SQL Server - Script to find particular column from all tables/views except system databases

Below script can be used to find particular column from all tables/views in all databases except system databases

IF OBJECT_ID('tempdb..##SearchColumn') IS NOT NULL
BEGIN
DROP TABLE ##SearchColumn
END
CREATE TABLE ##SearchColumn (ObjectName NVARCHAR(200))
EXECUTE sp_MSforeachdb '
IF ''?'' NOT IN (''master'',''model'',''Msdb'',''tempdb'') --system databases
BEGIN
    USE [?]
      INSERT ##SearchColumn
      SELECT Object_name(object_id) 
      FROM Sys.Columns 
      WHERE name LIKE ''%BusinessEntityID%'' --Pass ColumnName
END'
SELECT * FROM ##SearchColumn

See Also:

• My posts on T-SQL scripts