Display all the table names from all the databases if they contains specific column name

The following query will display all the table names from all the databases if they contains specific column name. Here my column name is 'Y', so I want retrived all the table names from all the databases which are having 'Y' as one of the column. I am taking column name 'Y' in to one variable @Column_Val, so it will give all the database names and their corresponding table names which consists 'Y' as a column.


DECLARE @CMD VARCHAR(1000)

DECLARE @Column_Val VARCHAR(1000)

set @Column_Val='Y'

create table #TableNames ( DatabaseName sysname, TableName sysname)

SET @CMD = 'USE [?]; SELECT DB_NAME()DATABASE_NAME,A.name as Tablename from
( (select * from SYS.Tables) A join (SELECT * FROM SYS.columns where name like''' +@Column_Val+ ''') B on A.object_id=B.object_id)'

insert into #TableNames EXEC SP_MSFOREACHDB @CMD
SELECT * FROM #TableNames Where DatabaseName not in ( 'master','msdb', 'tempdb', 'model' )
drop table #TableNames