SQL - Show Databases

In the dynamic scene of database management, having a good insight into the available databases for effective administration and development tasks. SHOW DATABASES command is designed to present all databases located on the server.

The purpose of exploring the SQL SHOW DATABASES command is to give database administrators and developers a basic tool that helps them manage and navigate their database environment more efficiently.

This article explores the purpose, syntax, and practical applications of the SHOW DATABASES command, along with examples to demonstrate its use in various SQL implementations like MySQL, PostgreSQL, and SQLite.

Understanding the SHOW DATABASES Command

The SHOW DATABASES command is a universal SQL query that retrieves and lists all databases in the current DBMS accessible to the user. This eliminates the need to directly query system tables and simplifies database management tasks. SQL specification of the `SHOW DATABASES` command is not complex and is simple enough to be used on different SQL universally. This is an easy-to-understand instruction of a database management system that fetches the list of all databases to which the current user has access and exhibits them. Here's the basic syntax:

Syntax

SHOW DATABASES;

Examples of SQL Show Databases

The Examples of SQL Show Databases demonstrate how to use the SHOW DATABASES command in various scenarios. These examples include listing all databases, filtering results with LIKE, and excluding specific databases using NOT LIKE

Example 1: Listing All Databases

This command returns the list of all the databases available on a server or a host and its output is in tabular format. Thus, this command has been used for displaying the following information about each existing database: Database (name)

Query:

SHOW DATABASES;

Output

Example 2: Filtering Databases Using LIKE Command

This statement filters out some items from a list based on certain criteria using the `LIKE` command. It only retrieves those with names containing ‘my’ as their starting characters.

Query:

SHOW DATABASES LIKE ' my % ' ;  

Output

Example 3: Filtering Databases with SHOW DATABASES Using NOT LIKE Command

This command gets rid of some items from a table using conditions specified by users. It brings back only those that do not have ‘test’ as part of their starting letters using the WHERE clause.

Query:

SHOW DATABASES WHERE `Database` NOT LIKE 'test%'  ;

Output

Conclusion

SQL, `SHOW DATABASES` command is among them that plays a vital role in getting information about the databases within the RBDMS. The simplified syntax and identical SQL structure fabric embedded in different implementations enable users to quickly obtain such a list of available databases for most operations like database administration, problem-solving, and data insight. By mastering this command and its advanced features, such as filtering with patterns and conditions, users can efficiently organize and navigate their database environments.