How to query the database itself (SQL)?

Question

SQL

Good day. I started to study SQL and came across such a question for myself (Google has not helped yet): how to make a certain query not to a table, but to the database itself.

Foreword: several tables have a field named, for example, productId, and to refer to a specific field, we write nameTable.productId. But we have a huge database and the field is called, for example, zIndex, and we don’t know if this name is in other tables (that is, you can simply refer to it by name if the name is unique, or through the table name).

Question: How can I make a request to the entire Database to display 1 field: zIndex, 2 field: the name of the tables where this field occurs.
I understand that it is better to specify the table name and not take a steam bath, but I want to find a solution.
Thanks to all

Answer 1

In general, no way. Individual DBMS may have their own mechanisms for working with schemas, but you should avoid using them.

Answer 2

MySQL. If you ever opened phpmyadmin and saw information_schema in the list of schemas. That is what you are looking for. This scheme contains the entire construct of the base. There you need to contact to pull out information about the architecture.

Select distinct table_schema, table_name
from information_schema.colums
where column_name = 'имя поля'

Oracle. There are also some "super-tables" with structure information:

SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE column_name = 'имя поля'

Each DBMS has its own source tables of the structure of the database itself, but they are implemented differently.