Wikibooks: MySQL/Language/Browsing the databases

• Format: Book
• Language: English
• Subjects: DML
• Online Access: https://en.wikibooks.org/wiki/MySQL/Language/Browsing_the_databases

= INFORMATION SCHEMA = information schema is a virtual database provided by MySQL 5 and later that contains metadata about the server and the databases. You can t modify structure and data of information schema . You can only query the tables. Many information schema tables provide the same data you can retrieve with a SHOW statement. While using SHOW commands is faster (the server responds much faster and you type less characters) the information schema provides a more flexible way to obtain and organize the metadata. = List databases = The INFORMATION SCHEMA table containing the databases information is SCHEMATA. The mysqlshow command line tool (DOS/Unix) can be used instead. You can t show databases if the server has been started with the skip all databases option. If you don t have the SHOW DATABASES privilege you ll only see databases on which you have some permissions. The following SQL commands provide information about the databases located on the current server. Show all databases SHOW DATABASES The SCHEMA keywords can be used in place of DATABASES. MySQL doesn t support standard SQL SCHEMAs so SCHEMA is a synonym of database. It has been added for compatibility with other DBMSs. =Add a filter on the databases names= SHOW DATABASES LIKE pattern The LIKE operator here works as in normal SELECTs or DML statements. So you can list all databases whose name starts with my SHOW DATABASES LIKE my% =Add complex filters= You can add more complex filters using the WHERE clause SHOW DATABASES WHERE conditions WHERE clause allows you to use regular expressions = operators string functions or other useful expressions to filter the records returned by SHOW DATABASES. = List tables and views = The following SQL commands provide information about the tables and views contained in a database. The INFORMATION SCHEMA tables containing this information are `TABLES` and `VIEWS`. Since the following statements provide very little information about views if you need to get metadata about them you ll probably prefer to query ...