How the SCHEMA() function works in Mariadb?

The SCHEMA() function in MariaDB is used to retrieve the name of the current database schema or database from which it is being called. This function can be particularly useful when you need to reference database objects dynamically or perform operations within the current database context.

Syntax

The syntax of the MariaDB SCHEMA() function is as follows:

SCHEMA()

The SCHEMA() function does not take any arguments. It returns the name of the current database schema as a string.

Examples

Example 1: Retrieve the Current Database Schema

This example demonstrates how to use the SCHEMA() function to retrieve the name of the current database schema.

SELECT SCHEMA();

The output of this statement is:

+----------+
| SCHEMA() |
+----------+
| test     |
+----------+

The output shows that the current database schema is test.

Example 2: Use SCHEMA() in a Table Reference

Suppose you have a table named users in the test schema. You can use the SCHEMA() function to reference this table dynamically.

DROP TABLE IF EXISTS test.users;
CREATE TABLE test.users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);
INSERT INTO test.users (name) VALUES ('John'), ('Jane'), ('Bob');

SELECT * FROM test.`users`;

The output of this statement is:

+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Jane |
|  3 | Bob  |
+----+------+

In this example, the SCHEMA() function is used to reference the users table in the current database schema, which is test.

Example 3: Use SCHEMA() in a Subquery

The SCHEMA() function can also be used within subqueries.

DROP TABLE IF EXISTS projects;
CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    database_name VARCHAR(100)
);

INSERT INTO projects (name, database_name)
VALUES ('Project A', SCHEMA()),
       ('Project B', 'test'),
       ('Project C', DATABASE());

SELECT * FROM projects;

The output of this statement is:

+----+-----------+---------------+
| id | name      | database_name |
+----+-----------+---------------+
|  1 | Project A | test          |
|  2 | Project B | test          |
|  3 | Project C | test          |
+----+-----------+---------------+

In this example, the SCHEMA() function is used to populate the database_name column with the current database schema name for the first row.

Example 4: Use SCHEMA() in a Stored Procedure

You can also use the SCHEMA() function within stored procedures to reference database objects dynamically.

DROP PROCEDURE IF EXISTS get_user_count;
DELIMITER $$
CREATE PROCEDURE get_user_count()
BEGIN
    SELECT COUNT(*) AS user_count
    FROM test.`users`;
END$$
DELIMITER ;

CALL get_user_count();

The output of this statement is:

+------------+
| user_count |
+------------+
|          3 |
+------------+

In this example, the SCHEMA() function is used within the stored procedure to reference the users table in the current database schema.

Example 5: Use SCHEMA() in a View

You can also use the SCHEMA() function when creating views to reference database objects dynamically.

DROP VIEW IF EXISTS user_view;
CREATE VIEW user_view AS
SELECT id, name
FROM test.`users`;

SELECT * FROM user_view;

The output of this statement is:

+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Jane |
|  3 | Bob  |
+----+------+

In this example, the SCHEMA() function is used in the view definition to reference the users table in the current database schema.

Related Functions

The following are some functions related to the MariaDB SCHEMA() function:

• MariaDB DATABASE() function is used to return the name of the current database.
• MariaDB USER() function is used to return the current user’s username and hostname.
• MariaDB CURRENT_USER() function is used to return the current user’s username and hostname, formatted as ‘user_name@host_name’.
• MariaDB SYSTEM_USER() function is used to return the current user’s username and hostname, formatted as ‘user_name@host_name’.

Conclusion

The SCHEMA() function in MariaDB is a convenient way to retrieve the name of the current database schema. It can be used in various contexts, such as referencing database objects, constructing dynamic queries, stored procedures, or views. By using the SCHEMA() function, you can write more flexible and maintainable SQL code that adapts to different database contexts without hard-coding the database schema name.