Tables and columns metadata

[egidiocaprino]

Hello.

Do you know anyway we can retrieve tables and columns metadata through the REST APIs? What we need is a list of existing tables and, for each table a list of columns with name and type.

sys_dictionary table should contains information about the columns but the labels are not human readable. I think I need to join another table but I cannot find any. Maybe sys_documentation but I cannot find the join field.

Can you please help us?

Thank you,

Egidio

[User140988]

Hi Egidio,

I see a field "element" with label "column name" on sys_dictionary.

You might not see this for the sys_dictionary records which say type as   "collection" which represent the dictionary of the table it self. Here the column name will be empty. if this is not helping, please share a screenshot of what you are seeing. Will be easy to understand.

Regards,

Chandana

[egidiocaprino]

You're right. The element is the API name of the column. Thank you!

The last thing I need is matching the table with the column. Is there any field indicating the table in the sys_dictionary table?

With this API call I'm getting all the columns named short_description: https://cloudreachdev.service-now.com/api/now/table/sys_dictionary?element=short_description

How can I detect from which table each column belongs?

Thank you for you help!

Egidio

[User140988 · Accepted solution]

The column should be "name" with label as "Table". This holds the table to which the dictionary entry is linked.

[egidiocaprino]

Perfect! Thank you very much Chandana.

[tibco]

Hi Chandana,

I have a similar issue and have gone through this post but couldn't get the answer. I am using sys_dictionary table to get a list of all the tables. Below is a sample response of "sys_dictionary" for one table which I have created:

{

          "calculation": "(function calculatedFieldValue(current) {\n\n\t// Add your code here\n\treturn '';   // return the calculated value\n\n})(current);",

          "sys_replace_on_upgrade": "false",

          "dynamic_ref_qual": "",

          "choice_field": "",

          "sys_updated_on": "2017-05-02 06:16:54",

          "spell_check": "false",

          "reference_cascade_rule": "",

          "reference": "",

          "sys_updated_by": "admin",

          "read_only": "false",

          "sys_created_on": "2017-05-02 06:16:54",

          "element_reference": "false",

          "sys_name": "id",

          "reference_key": "",

          "reference_qual_condition": "",

          "xml_view": "false",

          "dependent": "",

          "internal_type": {

              "link": "https://dev13196.service-now.com/api/now/table/sys_glide_object?name=string",

              "value": "string"

          },

          "sys_created_by": "admin",

          "element": "u_id",

          "max_length": "40",

          "use_dependent_field": "false",

          "delete_roles": "",

          "active": "true",

          "choice_table": "",

          "foreign_database": "",

          "sys_update_name": "sys_dictionary_u_tibco_u_id",

          "unique": "false",

          "name": "u_tibco",

          "dependent_on_field": "",

          "dynamic_creation": "false",

          "primary": "false",

          "sys_policy": "",

          "next_element": "",

          "virtual": "false",

          "widget": "",

          "use_dynamic_default": "false",

          "sizeclass": "",

          "mandatory": "false",

          "sys_class_name": "sys_dictionary",

          "dynamic_default_value": "",

          "sys_id": "3a4b70604f2232002fbce9628110c735",

          "write_roles": "",

          "array": "false",

          "audit": "false",

          "read_roles": "",

          "sys_scope": {

              "link": "https://dev13196.service-now.com/api/now/table/sys_scope/global",

              "value": "global"

          },

          "create_roles": "",

          "dynamic_creation_script": "",

          "defaultsort": "",

          "column_label": "id",

          "comments": "",

          "use_reference_qualifier": "simple",

          "display": "false",

          "reference_floats": "false",

          "sys_mod_count": "0",

          "default_value": "",

          "staged": "false",

          "reference_type": "",

          "sys_package": {

              "link": "https://dev13196.service-now.com/api/now/table/sys_package/global",

              "value": "global"

          },

          "attributes": "",

          "choice": "",

          "reference_qual": "",

          "sys_customer_update": "true",

          "table_reference": "false",

          "text_index": "false"

      },

Here, the "name" attribute with value "name":"u_tibco", is representing the table name. But how do I get the column names ( column labels) of this corresponding table? I see a field "column_label" but it has only one value "id". I also have a column "name" in this table which is not shown here.

Below is the screenshot of the table:

Thank you in advance!!!