I need to generate a list of tables in a database as well as the columns and their data types. Is there a node that can extract that information? I found node from Vernalis but it only seems to work for legacy database connections and possibly only extract list of tables for a schema.
Any ideas if there are nodes that would be able to extract the database metadata?
Thanks for the sugestions so far. My question is, when you connect to a database on the left hand of a DB Query Reader you can get list of database details that include schemas, tables and columns. How can we extract that without having to run a query. Surly the logic is already in the nodes to get that metadata from any database source we connect to.
If you look at the DB Table Selector node and you click on the Select a Table button you get the “Database Metadata Browser”
I am looking at getting this information. This would be very handy to create workflows that could check if tables already exists or if columns exists. One example would be to build a workflow that can be used to search for data in columns of a table. I know that different databases store this information, but this information is queried by the database nodes already, so if this metadata could be exported it won’t be needed to have logic to extract this database based on the database type. The information is already there, just need to be able to extract it into a JSON format or table format.
Hi @Willem , I think it would be a great feature to have indeed, and to your point, it does look like some existing nodes from Knime is able to get the DB Metadata indeed, so it might be something that Knime could salvage and create a node that can return the schema of a database
Hi @Daniel_Weikert ,
I have a workflow currently used to search for string values in a database which returns list of tables that contains the data. This is useful when you have to figure out where data in a database is located. Would be great if I could get the meta data out as then the workflow can be modified to run independently to which database source you are connected to.