SQL SCHEMAS

What is the SQL SHOW SCHEMAS command?

Lists all available schemas (databases or namespaces) in the current SQL environment.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SCHEMAS:

SQL SCHEMAS Full Explanation

The SCHEMAS keyword is most commonly used with the SHOW command (SHOW SCHEMAS) to return a result set containing every schema the connected user can see. A schema is a logical container that groups database objects such as tables, views, functions, and procedures. In MySQL and MariaDB, SHOW SCHEMAS is a direct synonym for SHOW DATABASES. In Snowflake, Redshift, BigQuery, and other modern warehouses, SHOW SCHEMAS lists namespaces inside the current database or account.Because SCHEMAS is an information-retrieval keyword, it reads from system catalogs without modifying data. Permissions determine visibility: users must have at least USAGE (or equivalent) rights on a schema for it to appear. Many dialects support optional filtering (LIKE, ILIKE, IN, STARTS WITH) so you can narrow the result set.Caveats:- Not part of the core ANSI SQL standard, so syntax varies.- In PostgreSQL the equivalent is SELECT schema_name FROM information_schema.schemata; there is no SHOW SCHEMAS command.- In MySQL, the command returns both system and user databases unless you filter them out.- Case sensitivity follows the rules of the underlying database engine.

SQL SCHEMAS Syntax

SHOW SCHEMAS;
-- dialect variations
SHOW SCHEMAS LIKE 'sales%';
SHOW SCHEMAS IN DATABASE my_db;

SQL SCHEMAS Parameters

LIKE pattern (string) - optional pattern for simple wildcard filtering.
ILIKE pattern (string) - case-insensitive pattern (Snowflake, Redshift).
IN database_name (identifier) - restricts listing to a specific database (Snowflake, BigQuery).
STARTS WITH prefix (string) - lists schemas whose names start with the prefix (Snowflake).
LIMIT n (integer) - limits number of rows returned (BigQuery).

Example Queries Using SQL SCHEMAS

-- List every schema the user can access
SHOW SCHEMAS;

-- Only schemas that begin with 'sales'
SHOW SCHEMAS LIKE 'sales%';

-- List schemas inside the marketing database (Snowflake)
SHOW SCHEMAS IN DATABASE marketing;

-- BigQuery example with row limit
SHOW SCHEMAS LIMIT 10;

Expected Output Using SQL SCHEMAS

A result set (table) with at least one column, usually named 'Database' or 'name', containing schema names visible to the session
Additional columns such as owner, created_on, and comment may appear depending on the dialect

Use Cases with SQL SCHEMAS

Quickly discover which schemas exist before writing cross-schema joins.
Audit environments to confirm a schema was created or dropped.
Feed code-generation tools or data catalogs that need to introspect available namespaces.
Filter schemas by prefix to locate temporary or environment-specific databases (e.g., 'test_%').

Common Mistakes with SQL SCHEMAS

Assuming SHOW SCHEMAS works in PostgreSQL (it does not).
Confusing schemas with databases; in MySQL they are equivalent, but not in Snowflake or Redshift.
Forgetting that permissions hide schemas; an empty result does not always mean the schema is absent.
Using ILIKE in MySQL or MariaDB where it is unsupported.

First Introduced In

MySQL 5.0 (as synonym for SHOW DATABASES)

Frequently Asked Questions

What is the difference between a schema and a database?

In MySQL they are interchangeable terms. In systems like Snowflake, Redshift, and PostgreSQL, a database can contain multiple schemas, each holding tables and other objects.

Does SHOW SCHEMAS require special permissions?

Yes. The user must have at least USAGE or equivalent permission on a schema for it to appear. Lacking privileges hides the schema from the result set.

Can I filter the list of schemas?

Most dialects allow filtering. Use LIKE or ILIKE with wildcards in MySQL, Snowflake, Redshift, and BigQuery. Snowflake also supports STARTS WITH and IN DATABASE clauses.

How do I get schema creation dates?

SHOW SCHEMAS in Snowflake and Redshift includes metadata columns like created_on. In MySQL you will need to query information_schema.schemata for that detail.