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 varchar(255) NOT NULL UNIQUE,
summary varchar(255),
FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE,
PRIMARY KEY(id)
);
CREATE TABLE authors_books (
author_id int NOT NULL,
book_id int NOT NULL,
FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE,
FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
PRIMARY KEY(author_id, book_id)
);
The end result should look something like:
authors
id,int,no,auto_increment
name,varchar(255),no,UNIQUE
PK(id)
books
id,int
author_id,int,FK->authors.id
title,varchar(255),UNIQUE
summary,varchar(255),NULL
PK(id)
authors_books
author_id,int,FK->authors.id
book_id,int,FK->books.id
PK(author_id,book_id)
Now, the following query gets me everything except the keys. It’s a mess in mysql output, but running it with the command helps it look digestible.
mysql -u root -p -NBre "SELECT CONCAT_WS('n', table_name, GROUP_CONCAT(
CONCAT_WS(',', column_name, column_type)
ORDER BY ordinal_position
SEPARATOR 'n'
),
'n'
)
FROM information_schema.columns
WHERE table_schema = 'test'
GROUP BY table_name"
I’ve tried many combinations to get the keys, but could only get as close as the following.
SELECT a.table_name, a.column_name, GROUP_CONCAT(CONCAT_WS(',', a.column_type, 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.column_name)
WHERE b.table_schema = 'test'
GROUP BY a.table_name, a.column_name
ORDER BY a.table_name;
Which gives:
| TABLE_NAME | COLUMN_NAME | GROUP_CONCAT(CONCAT_WS(',', a.column_type, b.constraint_name, b.ordinal_position) SEPARATOR '|') |
|---------------|-------------|------------------------------------------|
| authors | id | int,PRIMARY,1 |
| authors | name | varchar(255),name,1 |
| authors_books | author_id | int,PRIMARY,1|int,authors_books_ibfk_1,1 |
| authors_books | book_id | int,PRIMARY,2|int,authors_books_ibfk_2,1 |
| books | author_id | int,books_ibfk_1,1 |
| books | id | int,PRIMARY,1 |
| books | title | varchar(255),title,1 |
Notice, books.summary is missing, 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, 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 on the wrong track but.. How can I also get the columns that don’t have key constraints, plus the table’s primary key as its own row?
