Use table metadata for select statement in SQL Server?

Question

I have a large database and you want to select table names with a specific column name. I did something similar in MySQL but cannot find SQL Server information.

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 ANSI views information_schema, 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'

---

Change . Please note: this will not display views based on any tables with this column. If you only request INFORMATION_SCHEMA.COLUMNS, you will also get backviews.