Angie Angie - 4 years ago 83
SQL Question

Find all tables that have X column name

Is there a way to find all the tables that have an X column name within the Y database?

So

If X.Column Exists in Y.Database
Print all.tables with x.column

Thanks

Answer Source

Most, but not all, databases support the information_schema tables. If so, you can do:

select table_name
from information_schema.columns t
where column_name = YOURCOLUMNNAME;

If your database doesn't support the information_schema views, then any reasonable database has an alternative method for getting this information.

You may need to specify the database name, but that depends on the database. It could be:

select table_name
from YOURDATABASENAME.information_schema.columns t
where column_name = YOURCOLUMNNAME;

or

select table_name
from YOURDATABASENAME.information_schema.columns t
where column_name = YOURCOLUMNNAME and schema_name = YOURDATABASENAME;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download