Search for tables that has a specific column 3

[BenjaminLim]

Hi,

I like to search for all tables that contains "Employee ID" column. What would be the synthax like?

Please advice.

Thanks.

 

[MikeJones]

This is DB specific, if you are on oracle then you could do...

select table_name
from dbam_tables
where column_name = 'EMPLOYEE_ID'
/

 

[carp]

I can't find anything called dbam_tables, nor have I ever heard of this table.

For Oracle, I recommend

select table_name
from dba_tab_columns
where column_name = 'EMPLOYEE_ID';

 

[BenjaminLim]

Thanks Carp & MikeJones.

I tried the synthax you have provided but encounter "Object does not exist" message.

Kindly advice whether is this usually controlled by DBAs or ..

Thanks

Regards
Benjamin

 

[MikeJones]

Pardon my fingers, it should have read dba_tables, which as you point out is wrong anyway as it wouldn't contain any column info, however sidesteping my gross incompetence at typing if your getting that error then you proably don't have those views created (I don't know if they come as standard on an install). Carps suggestion should work though!

Assuming you are on an Oracle database then you could try...

SELECT table_name
FROM all_tab_columns
WHERE column_name = 'EMPLOYEE_ID'
/

If this won't work either then I'd ask what user you are logging in as and if they have DBA Privs on the DB?

HTH,

Mike.

 

[listener22]

or try sys.dba_tab_columns. Jim

oracle, vb