Sql to query a dbs scheme

In SqlServer. How do you query a db to bring back all the tables that have a field of a specific name?

From stackoverflow Dan

• Select Table_Name From Information_Schema.Columns
  Where Column_Name = 'YourFieldName'
  
  From Stu

• I'm old-school:

  select distinct object_name(id) from syscolumns where name = 'FIELDNAME'
  
  From Curt Hagenlocher

• The following query will bring back a unique list of Tables where Column_Name is equal to the column you are looking for:

  Select
  Table_Name
  From
  INFORMATION_SCHEMA.COLUMNS
  Where
  Column_Name = 'Desired_Column_Name'
  Group By
  Table_Name
  

  From GateKiller