Create database schema document
Jump to navigation
Jump to search
Create database schema document from existing MySQL database.
MySQL: Table document (Schema)[edit]
Sample result of mysql query using EXPLAIN syntax[1]: EXPLAIN <table>
Field | Type | Null | Key | Default | Extra
Sample result of mysql query using SHOW syntax[2]: SHOW FULL COLUMNS FROM <table>
Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment
Steps of create database schema document for MySQL
- Using web-based database management software such as: phpMyAdmin or Adminer
- SQL query: EXPLAIN <table> or SHOW FULL COLUMNS FROM <table>
- (for phpMyAdmin) Click "Print view (with full texts)"
- Copy the table to other word processors
MySQL: DDL document[edit]
Data definition language (DDL): Sample result of mysql query: SHOW CREATE TABLE <table>
CREATE TABLE `table` ( `id` varchar(30) NOT NULL, `note` varchar(30) DEFAULT NULL, `status` int(1) NOT NULL DEFAULT '1', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Tools: SQL syntax beautifier
PostgreSQL: Table document (Schema)[edit]
SELECT -- *, a.column_name AS "Field", a.data_type AS "Type", a.character_maximum_length AS "Length", a.is_nullable AS "Null", b.constraint_type AS "Key", a.column_default AS "Default" FROM information_schema.columns AS a LEFT JOIN ( SELECT c.column_name, c.data_type, constraint_type FROM information_schema.table_constraints tc JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name WHERE tc.table_name = '<MY_TABLE>' ) AS b ON b.column_name = a.column_name WHERE a.table_name = '<MY_TABLE>';
PostgreSQL: DDL document[edit]
PostgreSQL: Documentation: 14: pg_dump[3]
pg_dump -st <MY_TABLE> <MY_DATABASE> --schema-only > schema.sql
The parameter <MY_TABLE> is before than another parameter <MY_DATABASE>
References[edit]
Related pages: