Exploring MariaDB System Tables with SQL Statements

1. Information Schema
   This is a virtual database that provides information about the structure of your actual databases. It exposes details like tables, columns, data types, and relationships using standard SQL queries. You can't modify these tables directly.

2. Performance Schema
   This schema stores information about how MariaDB is performing. It includes metrics on things like queries, indexes, and connections. This data helps with performance tuning.

3. mysql Database
   This database contains tables specific to MariaDB server administration. They store information like user privileges, permissions, and database configurations.

4. Sys Schema (MariaDB 10.2+) : This schema holds internal system information specific to MariaDB. It's generally not recommended to modify tables here.

5. mariadb_schema (MariaDB 10.2+) : This schema contains MariaDB-specific system tables like binary logs and binary log position.

While you wouldn't typically program directly against these system tables, understanding their structure and contents can be helpful for database administration tasks. For instance, you can query the information schema to see a list of all tables in a database or check user permissions in the mysql database.

---

Get a list of all tables in a database (using Information Schema)

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';

This code queries the TABLES table within the information_schema schema. It filters the results to only show tables belonging to the database named your_database_name.

Get information about columns in a table (using Information Schema)

SELECT COLUMN_NAME, DATA_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'your_table_name';

This code retrieves the COLUMN_NAME and DATA_TYPE for each column in the table named your_table_name within the database your_database_name.

Check user privileges (using mysql database)

SELECT user, host, privilege
FROM mysql.user
WHERE user = 'username';

This code queries the user table inside the mysql database. It shows the privileges granted to the user named username based on their hostname.

---

1. Information Schema Views
   MariaDB offers views based on the information schema. These views pre-define specific queries that provide a focused subset of information from system tables. You can use these views instead of directly querying the underlying tables.

2. Stored Procedures and Functions
   You can write stored procedures or functions in MariaDB to encapsulate logic for retrieving or manipulating data related to your application. These functions can access system tables as needed, but hide the complexity from your application code.

3. Information Gathering Tools
   Several tools can be used to gather information about your MariaDB server and databases. These tools might offer alternative ways to access relevant data without directly querying system tables. Consider tools like mysqladmin or third-party monitoring solutions.

4. Custom Data Stores
   If you need to store application-specific data about your database (beyond what system tables offer), you can create custom tables within your user-defined databases. This approach allows you to tailor the data structure to your specific needs.

Choosing the Right Approach

The best approach depends on your specific needs.

• For storing application-specific data related to your database, custom tables within your user schemas might be the way to go.
• If you need to monitor performance or gather broader server information, consider dedicated tools.
• For complex logic or repeated tasks involving system tables, stored procedures or functions are a good option.
• If you simply need to retrieve information about your database structure, Information Schema views might suffice.