Solved Wanted Data dictionary display for MySQL (MariaDB) databases - Now solved

Hi folks
Is there a decent windows program which displays the data dictionary for tables in a MariaDB / MySQL database. PhpMyAdmin does it but I don't want to install that on Windows and I need this type of info on a Windows host. I know it can be done manually but that's a pain. I would like something like this that I can export into EXCEL workbooks. PhpMyAdmin especially over remote isn't that secure either.


there must surely be some I.T applications programmers out there somewhere who've done this type of thing.##

Thanks
jimbo

All geek to me



A Guy

A Guy said:

All geek to me

6 Data dictionary tools for MariaDB - DBMS Tools

List of tools that enable design and building of data dictionaries . Data Dictionary is a set of important information about data used within an organization (metadata). This information includes names, definitions, and attributes about data, owners, and creators of assets. Data Dictionary...

dbmstools.com

MySQL :: MySQL 8.0 Reference Manual :: 16.1 Data Dictionary Schema

dev.mysql.com

A Guy

Click to expand...

Hi there
thanks for the reply but the MySQL syyntax is not the same as MariaDB for the MySQL reference. The other tools are "free trial" only.

I think I'll see if I can do a bit of debugging with phpmyadmin to see how it does it -- one can get the source on git-hub as well -- give me something to do today.

Cheers

jimbo

Hi again
I've downloaded the source code -- there is a data dictionary section that looks if I run it on linux I can put a break point in it and try some old fashioned debugging -- not done for 20 years or more now !!!!!!




open the source index.twig : and putting a few break points might yield results.



I'd prefer to do it in Windows but I haven't a clue on how to compile / make etc stuff on windows or set break points. I've installed this on Linux from compile and it works so I'll eneter my break point, abend (crash) function re-compile and have a go with it and should have a play -- I can always cause a kernel crash with dump at the end of routine to get the generated sql function for the dictionary if step through fails,

Anyway off for an early lunch / late breakfast.

Cheers
jimbo

Hi folks

Marking now as essentially solved. Linux debug saved the day !!!!

Think I've got it essentially cracked.Easy enough to show all the DB's in the database.

so for each DB


1) get all the tables - again easy enough.

2) for each table show the main definition and indexes as follows




finally show the linked columns / foreign keys if any



Now all I need is a script to go through each database in turn run the queries in each table and push the output into a csv file or whatever for excel. I've done the hard bit in getting the data - the other should just be elementary "loop" type coding.

I'm marking as Solved now as I've got all the required data and done the hard bit. The rest is just bash scripts etc. - if anybody wants to do a Windows project on that -- fair do's.

By the way easy enough to run the sql queries on windows from a remote Linux server hosting MariaDB -- just SSH into the server and run the commands as per above from the windows command line. You need to ensure though the MarioaDB / Mysql DB allows remote access if server is not local -- change that in the mariadb / mysql config file (or get the DBA admin to do it if you don't have those rights. On a test laptop shouldn't be a problem to a test server - Windows can even be a VM.

change config to : (uncomment bind address and restart the DB server).
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0 <======================
#
# Optional setting
etc etc

I think I deserve some Beer after that one. !!!! Or even something stronger -- maybe some Scottish "Amber Nectar". !!


Cheers
jimbo