A production MySQL database rarely explains itself. A table name may hint at its purpose, but it will not tell you which columns are required, where foreign keys point, which indexes support a report, or whether a trigger changes data behind the application’s back. Knowing how to get MySQL database schema details gives your team a reliable starting point before changing a legacy PHP application, integrating a new platform, or troubleshooting a slow workflow.
In MySQL, “schema” can mean the database itself, its tables and columns, or the full structural definition that includes indexes, constraints, views, routines, triggers, and events. The right extraction method depends on what you need to inspect and whether the result is for a quick investigation, documentation, migration, or source control.
Start by defining the schema detail you need
For a single table, you may only need its columns, data types, defaults, indexes, and foreign keys. For a handoff or migration, you need a complete data definition language, or DDL, export. If an application behaves unexpectedly, stored procedures, triggers, and views may matter as much as tables.
This distinction prevents a common mistake: running DESCRIBE table_name, seeing a list of fields, and assuming you have the schema. DESCRIBE is useful, but it does not provide the complete CREATE TABLE statement, every constraint definition, trigger, or routine. Treat it as a quick inspection command, not a complete documentation process.
Before extracting anything, connect to the correct server and database. Development, staging, and production can differ materially, especially in long-lived systems where a hotfix was applied directly to one environment. Record the hostname, MySQL version, database name, and extraction date with any schema output you intend to keep.
How to get MySQL database schema from the command line
The MySQL client is the fastest way to inspect structure when you have database access. Start by listing available databases:
SHOW DATABASES;
Then select the database and list its tables:
USE customer_portal;
SHOW FULL TABLES;
SHOW FULL TABLES identifies whether each object is a base table or a view. That matters because a view can look like a table to application code while depending on several underlying tables.
For a quick field-level view, use either of these commands:
DESCRIBE orders;
SHOW FULL COLUMNS FROM orders;
SHOW FULL COLUMNS returns more metadata, including comments and collation. It is useful when a database has been maintained by several teams and column comments contain business context that the application code does not.
For the actual table definition, use SHOW CREATE TABLE:
SHOW CREATE TABLE orders;
This is one of the most useful commands in MySQL. Its output includes column definitions, primary keys, unique keys, secondary indexes, storage engine, character set, collation, and foreign-key constraints supported by the table. It is generally more trustworthy than manually reconstructing a schema from a spreadsheet or a visual diagram.
Inspect indexes separately when performance is the immediate concern:
SHOW INDEX FROM orders;
A missing composite index can explain why a query performs adequately with test data but slows down sharply once the business has accumulated real transaction volume.
Do not stop at tables when examining application behavior. Check views, triggers, and stored programs as needed:
SHOW CREATE VIEW active_customers;
SHOW TRIGGERS;
SHOW PROCEDURE STATUS WHERE Db = 'customer_portal';
SHOW FUNCTION STATUS WHERE Db = 'customer_portal';
For a specific stored procedure or function, use SHOW CREATE PROCEDURE procedure_name or SHOW CREATE FUNCTION function_name. Triggers and routines often contain business rules that are otherwise invisible during a code review.
Query INFORMATION_SCHEMA for a complete inventory
MySQL exposes metadata through the INFORMATION_SCHEMA database. This is the practical choice when you need structured results for documentation, audits, reports, or automated checks. Instead of copying output one table at a time, you can query the catalog with SQL and export the results.
To list all tables and views in a database:
SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'customer_portal'
ORDER BY TABLE_NAME;
To retrieve columns across every table, including data types, nullability, defaults, and comments:
SELECT TABLE_NAME,
ORDINAL_POSITION,
COLUMN_NAME,
COLUMN_TYPE,
IS_NULLABLE,
COLUMN_DEFAULT,
COLUMN_KEY,
EXTRA,
COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'customer_portal'
ORDER BY TABLE_NAME, ORDINAL_POSITION;
That query is particularly valuable for legacy systems. It quickly exposes inconsistent naming, duplicate concepts stored in different formats, nullable fields that the application assumes are required, and columns that appear unused but may feed integrations or scheduled jobs.
For relationships, query the key usage and constraint tables:
SELECT kcu.TABLE_NAME,
kcu.COLUMN_NAME,
kcu.REFERENCED_TABLE_NAME,
kcu.REFERENCED_COLUMN_NAME,
rc.CONSTRAINT_NAME,
rc.UPDATE_RULE,
rc.DELETE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
ON rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE kcu.TABLE_SCHEMA = 'customer_portal'
AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY kcu.TABLE_NAME, kcu.ORDINAL_POSITION;
This output provides the raw material for an entity relationship diagram. It also reveals an uncomfortable but common reality: application-level relationships may exist without foreign keys. If customer_id appears across several tables but no constraint enforces it, document that dependency before cleaning up data or deleting records.
INFORMATION_SCHEMA is excellent for inventory and automation, but it is not always the best way to recreate DDL exactly. For an authoritative create statement, use SHOW CREATE or a schema-only dump.
Use mysqldump when you need portable DDL
For a full schema export, mysqldump is the dependable command-line option. The following command exports table definitions without row data:
mysqldump -u username -p --no-data --routines --triggers --events customer_portal > customer_portal_schema.sql
The resulting SQL file can be reviewed, committed to source control, compared between environments, or used to build a clean database. --no-data keeps the export focused on structure. --routines, --triggers, and --events are explicit because these objects are easy to omit and can be operationally significant.
There are trade-offs. A schema dump may include statements such as DROP TABLE depending on the options used, so review it before running it anywhere. It may also contain server-specific details, definer clauses, character set settings, and SQL modes that complicate restoration on another server. For a migration between environments, test the import in a nonproduction database first.
Be careful with permissions. A user that can read tables may not have permission to view routine bodies, trigger definitions, or all metadata. Missing objects in an export are not proof that they do not exist. They may indicate insufficient privileges. Ask for a least-privilege account that can inspect definitions, rather than using shared administrative credentials.
Use MySQL Workbench for visual inspection
MySQL Workbench is useful when stakeholders need a visual model rather than SQL output. Its reverse-engineering workflow can connect to an existing database, read tables and relationships, and create an EER diagram. This helps operations teams and non-database developers understand the high-level structure before discussing a change.
Visual diagrams have limits. Large databases can produce diagrams that are technically complete but difficult to read. Foreign keys that are absent from the database will not appear, and a diagram does not show why a field exists or how the application uses it. Use the diagram alongside the SQL definitions and application code, not as a replacement for either.
Turn schema extraction into maintainable documentation
Getting the schema is only the first step. The useful outcome is a current, reviewable record that helps the next developer make safe decisions. Store schema-only dumps in version control when practical, and generate a metadata report for teams that need a searchable data dictionary.
For business-critical tables, add context that MySQL cannot infer: ownership, retention requirements, source systems, sensitive fields, and known integration dependencies. A column named status is not meaningful documentation unless someone explains valid values, who changes them, and what downstream process depends on each state.
When working on a legacy PHP or e-commerce system, compare the extracted schema against the application’s migrations, installation scripts, and live queries. If those sources disagree, production behavior is usually the immediate operational truth, but the discrepancy should be resolved deliberately. Silent schema drift is a long-term maintenance cost.
A clean schema inventory gives engineers a factual basis for changes instead of assumptions. Before modifying a table, replacing an integration, or rebuilding an internal portal, extract the DDL, identify dependent objects, and verify the result against the environment that actually runs the business.
Frequently Asked Questions
- DESCRIBE gives a quick list of columns and types — useful for a fast check, but incomplete. SHOW CREATE TABLE returns the full table definition, including primary keys, unique keys, indexes, storage engine, character set, and foreign key constraints. Treat DESCRIBE as a glance, not documentation.
- Run SHOW FULL TABLES; after selecting the database. It distinguishes base tables from views, which matters because views can look like tables to application code while actually depending on multiple underlying tables.
- Query INFORMATION_SCHEMA.COLUMNS filtered by TABLE_SCHEMA. This returns data types, nullability, defaults, keys, and comments across all tables in one structured result — much faster than running DESCRIBE table by table, especially useful for legacy systems.
- Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE with INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS. Note that application-level relationships may exist without enforced foreign keys — if a column like customer_id appears across tables without a constraint, document that dependency before assuming it's safe to change.
- Use mysqldump with --no-data --routines --triggers --events database_name > schema.sql. These flags are explicit because routines, triggers, and events are easy to omit and often contain important business logic.
- It could be a permissions issue, not evidence the objects don't exist. A user with table read access may lack permission to view routine bodies or trigger definitions. Request a least-privilege account specifically able to inspect definitions.
- Not on its own. Reverse-engineered EER diagrams are useful for high-level discussions with non-database stakeholders, but foreign keys absent from the database won't appear, and diagrams don't explain why a field exists or how the application uses it. Pair diagrams with SQL definitions and application code.
- Production behavior is usually the immediate operational truth, but the discrepancy shouldn't be ignored. Silent schema drift between documented migrations and the live database is a long-term maintenance risk and should be resolved deliberately.
