Create database schema document

Create database schema document from existing MySQL database.

MySQL: Table document (Schema)

Sample result of mysql query using EXPLAIN syntax: EXPLAIN <table>

Field | Type | Null | Key | Default | Extra

Sample result of mysql query using SHOW syntax: SHOW FULL COLUMNS FROM <table>

Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment

Steps of create database schema document for MySQL

1. Using web-based database management software such as: phpMyAdmin or Adminer
2. SQL query: EXPLAIN <table> or SHOW FULL COLUMNS FROM <table>
3. (for phpMyAdmin) Click "Print view (with full texts)"
4. Copy the table to other word processors

MySQL: DDL document

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

• Instant SQL Formatter

PostgreSQL: Table document (Schema)

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

PostgreSQL: Documentation: 14: pg_dump

pg_dump -st <MY_TABLE> <MY_DATABASE> --schema-only  > schema.sql

The parameter <MY_TABLE> is before than another parameter <MY_DATABASE>

References

Related pages:

• Data type