Database Info
MySQL 8.0.22
InnoDB
The Goal
I'm trying to get all tables in a database, with their column definitions and foreign keys, and primary keys on their own row for each table group.
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 '|') as the_rest
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 | the_rest |
|---------------|-------------|------------------------------------------|
| 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?
Update
This query from @J.D. also includes the columns with no constraints, (modified to exclude the 3000+ irrelevant results)
SELECT c.table_name, c.column_name, k.the_rest
FROM columns c
JOIN (
SELECT a.table_name, a.column_name,
GROUP_CONCAT(CONCAT_WS(',', a.column_type, b.constraint_name, b.ordinal_position ) SEPARATOR '|') as the_rest
FROM columns a
LEFT JOIN key_column_usage b
ON (a.table_name = b.table_name AND a.column_name = b.column_name AND b.table_schema = 'test')
GROUP BY a.table_name, a.column_name
ORDER BY a.table_name
) as k
ON c.table_name = k.table_name AND c.column_name = k.column_name
WHERE c.table_schema = 'test'
However
- it still does not put the primary key(s) into its own row
- my modification somehow removed the foreign keys from the
the_rest
column
here's what it looks like:
| TABLE_NAME | COLUMN_NAME | the_rest |
|---------------|-------------|---------------------|
| authors | id | int,PRIMARY,1 |
| authors | name | varchar(255),name,1 |
| authors_books | author_id | int |
| authors_books | book_id | int |
| books | author_id | int |
| books | id | int,PRIMARY,1 |
| books | summary | varchar(255) |
| books | title | varchar(255) |
This query from the resource @J.D. linked shows the keys with the primary keys on their own row and grouped for composite keys. But, if the column is the first key in a composite key it's not included in the set as its own record. Which is bad because because you can't see the foreign key info (linked table and column).
select tab.table_schema as database_name,
sta.index_name as pk_name,
group_concat(distinct sta.column_name order by sta.column_name) as 'columns',
tab.table_name,
sta.non_unique as is_FK
from information_schema.tables as tab
inner join information_schema.statistics as sta
on sta.table_schema = tab.table_schema
and sta.table_name = tab.table_name
-- and sta.index_name = 'primary'
where tab.table_schema = 'test'
and tab.table_type = 'BASE TABLE'
group by tab.table_name, sta.index_name, sta.non_unique
order by tab.table_name;
And the output:
| database_name | pk_name | columns | TABLE_NAME | is_FK |
|---------------|-----------|-------------------|---------------|-------|
| test | name | name | authors | 0 |
| test | PRIMARY | id | authors | 0 |
| test | book_id | book_id | authors_books | 1 |
| test | PRIMARY | author_id,book_id | authors_books | 0 |
| test | author_id | author_id | books | 1 |
| test | PRIMARY | id | books | 0 |
| test | title | title | books | 0 |
Best Answer
The reason your last query is filtering out columns without constraints like
books.summary
is because yourWHERE
clause inadvertently filters out anything that doesn't match on theJOIN
clause. You should actually move the predicate from theWHERE
clause up into yourJOIN
clause's predicates like so:This will ensure your
LEFT JOIN
returns all rows from thecolumns
table and willNULL
out any values of the columns in thekey_column_usage
table that don't match on theJOIN
(i.e. the rows where thecolumns
don't have a constraint).Also, have you seen this article List all primary keys (PKs) and their columns in MySQL database and tried the sample code from it? Although they're filtering on primary keys you can adjust that filter and likely access other constraints like foreign keys as well. Please see a modified query below: