Subscribe to this thread
Home - General / All posts - sql : list all field names from table
yves61
438 post(s)
#30-Aug-22 12:25

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

adamw


10,447 post(s)
#31-Aug-22 09:06

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.)

yves61
438 post(s)
#31-Aug-22 09:48

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 ?

adamw


10,447 post(s)
#31-Aug-22 10:04

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.

sga30 post(s)
#02-Sep-22 14:35

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'

yves61
438 post(s)
#02-Sep-22 14:59

@ 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]

yves61
438 post(s)
#03-Sep-22 06:31

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 ?

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.