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'

Answer 2

Here you go:

SELECT C.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS C
  INNER JOIN INFORMATION_SCHEMA.TABLES AS T ON C.TABLE_NAME = T.TABLE_NAME
    AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE C.COLUMN_NAME = 'classtypeid'
  AND T.TABLE_TYPE = 'BASE TABLE'

---

Edit: Note that this will not list views based on any tables with that column. If you only query INFORMATION_SCHEMA.COLUMNS you will also get back views.