Use table metadata for select statement in SQL Server?

Question

I have a large database and would like to select table names that have a certain column name. I have done something like this in MySQL, but can't find any info on SQL Server.

I want to do something like:

select [table] 
from [db] 
where table [has column 'classtypeid']

How can I do something like this?

Answer 1

Use the ANSI information_schema views, this will also work in MySQL

select table_name 
from information_schema.columns 
where column_name = 'classtypeid'