How to extract structure of DB?

[Yogi Yang]

Hello,

How can we extract the structure of opened database like Tables, Views, etc.

I want to show the list of tables to user and then when the user clicks on a table I want to retrieve/get the table structure and present it to user.

How can we do this?

[marsupilami]

Use a TZSqlMetadata object. See Zeos Docunentation collection for more information.

[aducom]

The docs say "2.5.8 TZSQLMetadata
With this special TDataSet component it is possible to access the metadata
of a database like tables, columns, etc. (This chapter is still to be expanded!)"

So that is still work in progress. Using the Metadata component works fine. However I was not able to get a list of views. Well, my views in MySQL list 3 views, but only one is shown as a regular table?

How to get a list of views?

[aehimself]

As far as I know SQLMetaData only provides a standardized output, in the background it has a query for each RDBMS, each metadata type. If you put an SQLMonitor and log it's output when loading a metadata, you'll see the actual query which was being executed to get the information.

Because of this reason, there might be empty / wrongly defined / outdated queries (e.g. worked 3 major versions ago). You can manually execute that command in a TZQuery object / any DB manager application to check why the results are incomplete.

For MySQL it should be something like:

Code: Select all

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

For SQLite:

Code: Select all

SELECT name FROM sqlite_master WHERE type = 'view' AND sql LIKE "% FROM %tablename% WHERE %";

[marsupilami]

So that is still work in progress. Using the Metadata component works fine. However I was not able to get a list of views. Well, my views in MySQL list 3 views, but only one is shown as a regular table?

How to get a list of views?

I seem to remember that views are listest as tables. One has to filter by the TABLE_TYPE column to restrict it to views. If TZSQLMetadata only lists one view and you have three views in your database, we would need some kind of example to find out why some views get listed and why others don't get listed.