ON c.table_name = k.table_name AND c.column_name = k.column_name ON (a.table_name = b.table_name AND a.column_name = b.column_name AND b.table_schema = 'test') GROUP_CONCAT(CONCAT_WS(',', a.column_type, b.constraint_name, b.ordinal_position ) SEPARATOR '|') as the_rest This query from also includes the columns with no constraints, (modified to exclude the 3000+ irrelevant results) SELECT c.table_name, c.column_name, k.the_rest
SQL TABS SELECT ALL TABLES UPDATE
How can I also get the columns that don't have key constraints, plus the table's primary key as its own row? Update
Notice, books.summary is missing, so I'm thinking all fields without a constraint will be missing from the results.Īnother 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. | books | author_id | int,books_ibfk_1,1 | | authors_books | book_id | int,PRIMARY,2|int,authors_books_ibfk_2,1 | | authors_books | author_id | int,PRIMARY,1|int,authors_books_ibfk_1,1 | Which gives: | TABLE_NAME | COLUMN_NAME | the_rest | ON (a.table_name = b.table_name AND a.column_name = b.column_name) SELECT a.table_name, a.column_name, GROUP_CONCAT(CONCAT_WS(',', a.column_type, b.constraint_name, b.ordinal_position ) SEPARATOR '|') as the_rest I've tried many combinations to get the keys, but could only get as close as the following. mysql -u root -p -NBre "SELECT CONCAT_WS('\n', table_name, GROUP_CONCAT(
It's a mess in mysql output, but running it with the command helps it look digestible. Now, the following query gets me everything except the keys. The end result should look something like: authors Given the following tables create database test įOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE,įOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE, 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.
0 kommentar(er)
