Query Field Names Of All Tables

Question

Here's a fun one.

Occasionally we may want to find the tables consisting of certain fields, this is because our schema's are so large, and our affiliation with external entities for database definitions causes a bit of confusion when composing certain queries.

Sometimes I need to know the answer to the question of: "What tables in database X contains the field name of Y?"

Querying schemas isn't my forté, and neither is finding search criteria to yield something mildly useful.

Cheers

Answer 1

I use this:

select * from sys.tables
where object_id in
(
    select object_id from sys.columns where [name] = 'FieldName'
)

Answer 2

I would use this query:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'ColumnName'