Query - to find specific column across tables and across database

Here is the situation my friend came up with -

list all the databases and tables with containing one specific column

he required this info because his project was under migration and they were looking for this kind of information .. The query shared here can be modified to find Most commonly used column name across databases

Query does 2 things

• Loops over databases having test at the start of their name
• Finds out the column across tables using Information_schema.Columns

IF object_id('tempdb..#t') is not null

drop table #t

create table #t

(

dbname varchar(1000),

table_name varchar(1000)

)

insert into #t (dbname,table_name)

exec sp_msforeachdb 

'

use ?

IF (''?'' in (select name from sys.databases where name like ''test%''))

begin

print ''?''

select ''?'' ,table_name

from ?.information_schema.columns

where column_name =''studentid'' and table_name like ''%test''

end

'

select * from #t