System tables in databases

Rarely, when you have to do an enumeration of all the tables, columns, rights - during the development this is done by the client program. But when development is carried out remotely and you do not see differences in duplicated databases in principle of security, then in order to move from the dead point of fortune-telling, what’s the matter, why do not the data arrive, you need to do basic queries about the structure of tables and privilege possessions.

The key are tn. system tables that describe the rest and themselves. Due to the lack of such standards in ANSI SQL, different engines implemented access to such tables in different ways.

Mysql is a separate type of request. SHOW can also show the state of the database, process load and so on.
SHOW TABLE status
')
Postgre - The hierarchy is divided according to the database.schema.table principle, so administrators often don’t like to create new databases - the pg_catalog and information_schema system schemes are duplicated for them each time.
select table_name, table_schema from information_schema.tables // all tables
select column_name from information_schema.columns WHERE table_name = 'columns' // vicious circle

Oracle - ordinary tables, which though very few people can just take and change

select * from ALL_TABLES
select * from USER_TABLES

- Original