Create database schema document: Difference between revisions
Jump to navigation
Jump to search
no edit summary
mNo edit summary |
No edit summary |
||
| Line 2: | Line 2: | ||
Create database schema document from existing MySQL database. | Create database schema document from existing MySQL database. | ||
== Table document == | == MySQL: Table document (Schema) == | ||
Sample result of mysql query using {{kbd | key=EXPLAIN}} syntax<ref>[https://dev.mysql.com/doc/refman/8.0/en/explain.html MySQL :: MySQL 8.0 Reference Manual :: 13.8.2 EXPLAIN Syntax]</ref>: {{kbd | key =<nowiki>EXPLAIN <table></nowiki>}} | Sample result of mysql query using {{kbd | key=EXPLAIN}} syntax<ref>[https://dev.mysql.com/doc/refman/8.0/en/explain.html MySQL :: MySQL 8.0 Reference Manual :: 13.8.2 EXPLAIN Syntax]</ref>: {{kbd | key =<nowiki>EXPLAIN <table></nowiki>}} | ||
<pre> | <pre> | ||
| Line 18: | Line 18: | ||
# Copy the table to other word processors | # Copy the table to other word processors | ||
== DDL document == | == MySQL: DDL document == | ||
[https://en.wikipedia.org/wiki/Data_definition_language Data definition language] (DDL): Sample result of mysql query: {{kbd | key =<nowiki>SHOW CREATE TABLE <table></nowiki>}} | [https://en.wikipedia.org/wiki/Data_definition_language Data definition language] (DDL): Sample result of mysql query: {{kbd | key =<nowiki>SHOW CREATE TABLE <table></nowiki>}} | ||
<pre> | <pre> | ||
| Line 32: | Line 32: | ||
* [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter] | * [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter] | ||
== PostgreSQL: Table document (Schema) == | |||
<pre> | |||
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>'; | |||
</pre> | |||
== References == | == References == | ||