Home -
General /
All posts - sql : list all field names from table
|
with M9 : I am looking for sql coding to list all field names from all table components but did not cross any yet. Any hints are welcome. With SQL Server the following will work , but not with M9. SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employees' I know mfd_root table lists table components and mfd_meta table will under circumstances list the table_names and field_names but not standard. I do not quite understand when (under which circumstances) mfd_meta will hold information and when not. Is there a way to tell mfd_meta to list all table_names and field_names from all tables ? ******** added : test.map file Attachments: test.map
|
|
There's no way to enumerate table fields from SQL. Why do you need that? (Maybe there's a different way to do what you want.)
|
|
The aim is to create in ms-access a form with multiple tables and multiple fields to select in order to generate queries. I was thinking M9 SQL could be a handy tool to list all field names from all tables in the project. For this, I was thinking to use "mfd_meta" to fetch table names and field names but it looks like mfd_meta does not show them straight. But maybe , is there a way ?
|
|
There is a way, yes, you can write a script function that would take a table and return the list of its fields as another table. Then you can call that script function from a query. In the future, we will likely add a built-in function to do that. Here is an example of writing a script function that takes and returns a table: random sample from a point cloud.
|
|
Hi, To answer the second part of your question as per under which circumstances mfd_meta will hold information about fields, here is my experience: Whenever you change the width of a column in a table (for example to be able to visualise the whole text inside a column), then all columns of the table will appear in mfd_meta with Property Item.0, Item.1 etc... and you will have the name of your fields within the Value field. You will then have to extract your names from the json array, something like with this query (change tableName with the name of your table). Yet remember that you will need to change the column width prior executing this query and I don't know if one automate this change of column width in a query or script SELECT * FROM (SELECT SPLIT CALL StringToJsonObjectValues([Value]) FROM mfd_meta WHERE Name like 'a') AS sub WHERE Name LIKE 'Field'
|
|
@ sga: Thank you. For the table components and table fields listed in mfd_meta I found this sql query working too : SELECT [Name] as tableName, StringJsonValue([Value], 'field', FALSE) as [fieldName] FROM [mfd_meta]
|
|
Found this link and script useful ! https://manifold.net/doc/mfd9/tools_-_add-ins.htm Using only this part : ... if (type == "table") ... Next step would be how to get the output file back in manifold in a table. Or better not first write a txt file outside Manifold, but straight inside M9 project as a table . Any ideas ?
|