SQL: How to get all tables' columns & keys + the primary key?

Question

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 |

Answer 1

The reason your last query is filtering out columns without constraints like books.summary is because your WHERE clause inadvertently filters out anything that doesn't match on the JOIN clause. You should actually move the predicate from the WHERE clause up into your JOIN clause's predicates like so:

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 AND b.table_schema = 'test')
GROUP BY a.table_name, a.column_name
ORDER BY a.table_name;

This will ensure your LEFT JOIN returns all rows from the columns table and will NULL out any values of the columns in the key_column_usage table that don't match on the JOIN (i.e. the rows where the columns 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:

select tab.table_schema as database_schema,
    sta.index_name --as pk_name,
    sta.seq_in_index as column_id,
    sta.column_name,
    tab.table_name
from information_schema.tables as tab
left 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' --commented out to get all indexes (probably want to filter on certain types though
where tab.table_schema = 'your database name'
    and tab.table_type = 'BASE TABLE'
order by tab.table_name,
    column_id;