I’m trying to get all tables in a database, with their column definitions and keys. Given the following tables create database test; use test; CREATE TABLE authors ( id int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL UNIQUE, PRIMARY KEY(id) ); CREATE TABLE books ( id int NOT NULL AUTO_INCREMENT, author_id int NOT NULL, title […]
- Tags `name` varchar(255) NOT NULL UNIQUE, `title`, ', '\n' ) FROM information_schema.columns WHERE table_schema = 'test' GROUP BY table_name" I've tried many combinations to get the keys, "No"), 1 | | authors | name | varchar(255), 1 | | authors_books | author_id | int, 1 | | authors_books | book_id | int, 1 | | books | author_id | int, 1 | | books | id | int, 1 | | books | title | varchar(255), 1 | Notice, 1|int, 2=int, a.column_name, a.column_name ORDER BY a.table_name; Which gives: | TABLE_NAME | COLUMN_NAME | GROUP_CONCAT(CONCAT_WS(', a.column_type, author_id int NOT NULL, authors_books_ibfk_1, authors_books_ibfk_2, auto_increment name, b.constraint_name, b.ordinal_position ) SEPARATOR '|') FROM columns a LEFT JOIN key_column_usage b ON (a.table_name = b.table_name AND a.column_name = b.colum, b.ordinal_position) SEPARATOR '|') | |---------------|-------------|------------------------------------------| | authors | id, book_id int NOT NULL, book_id) ); The end result should look something like: authors id, book_id) Now, books_ibfk_1, books.summary is missing, but could only get as close as the following. SELECT a.table_name, but running it with the command helps it look digestible. mysql -u root -p -NBre "SELECT CONCAT_WS('\n', COLUMN_NAME, column_type) ORDER BY ordinal_position SEPARATOR '\n' ), FK->authors.id book_id, FK->authors.id title, FK->books.id PK(author_id, FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE, FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE, GROUP_CONCAT( CONCAT_WS(', GROUP_CONCAT(CONCAT_WS(', I'm trying to get all tables in a database, int, int author_id, Name, NULL PK(id) authors_books author_id, plus the table's primary key as its own row?, primary, PRIMARY KEY(author_id, PRIMARY KEY(id) ); CREATE TABLE authors_books ( author_id int NOT NULL, PRIMARY KEY(id) ); CREATE TABLE books ( id int NOT NULL AUTO_INCREMENT, so I'm thinking all fields without a constraint will be missing from the results. Another thing is when a primary key is a composite key, such as in the bridge table authors_books, summary varchar(255), table_name, the following query gets me everything except the keys. It's a mess in mysql output, they should not be part of the GROUP_CONCAT in the 3rd column but instead be its own record at the end of the table_name group. I might be o, title varchar(255) NOT NULL UNIQUE, UNIQUE PK(id) books id, UNIQUE summary, varchar(255), with their column definitions and keys. Given the following tables create database test; use test; CREATE TABLE authors ( id int NOT NUL