Given a column name how can I find which tables in database contain that column ?

Question

• Given a column name how can I find which tables in database contain that column ?

  or alternatively

• How can I find that particular column exists for all tables in Database ?

Note: Kindly explain answers with Examples as that I get most knowledge from the answer.

Edit: I am using MySQL Database.

Answer 1

Depends on the database you are using. Many database systems expose a set of tables of views that contain details of the schema. For example, you can get schema information from the SYSTABLE and SYSCOLUMN views in Sybase ASA.

Answer 2

in SQL Server:

select distinct t.name
from sys.Columns c 
    inner join sys.tables t on c.object_id = t.object_id
where c.name = 'YOUR_COLUMNNAME'

Answer 3

SELECT * FROM information_schema.columns WHERE COLUMN_NAME = 'mycolumn'