SQL (SQL server) query to list table details..

I have created a number of SQL queries in ssms to list table details, and the app/espace they are related to.
See Code sample below....

I'm wondering if someone can advise table(s) related to Data Type in Outsystems...
When I run the query below, in the "Field Type" Column I'm getting a lot of fields with text Similar to: bt29049529-f47d-4a6b-bda0-63d3dff02e3b*24ace1e0-e2.. I'd like to be able to link these to human readable text, like:  "Rhubarb Record Identifier", or similar.. 

(Is there a better way to do this??  I find the extracts great, because they list APP, ESPACE, data Kind, table name, phyical table name, and also details of the table..)

(I have a similar query - attached which gets column information from the INFORMATION_SCHEMA.COLUMNS which lists actual data types, but would like the outsystems details..)

eg:
SELECT
       APP.NAME "App Name",
       /* app.DESCRIPTION, */
      /* modu.ESPACE_ID, */
      ESP.NAME "ESpace Name",
      /* ENT.ID "Entity ID", */
      ENT.Data_Kind,
      ENT.NAME "Table Name",
      ENT.PHYSICAL_TABLE_NAME,
      ea.NAME "Field Name",
      ea.DESCRIPTION,
      isnull(bt.Name, ea.type) "Field Type",
      ea.length "Field Length",
      ea.decimals,
      ea.DEFAULT_LABEL,
      ea.DEFAULT_VALUE
  FROM CATALOG.dbo.OSSYS_APPLICATION APP,
       CATALOG.dbo.OSSYS_APP_DEFINITION_MODULE APP_MOD,
       CATALOG.dbo.OSSYS_MODULE MODU,
       CATALOG.dbo.OSSYS_ESPACE ESP,
       CATALOG.dbo.OSSYS_Entity ENT,
       CATALOG.dbo.OSSYS_Entity_attr EA
  LEFT JOIN CATALOG.dbo.OSSYS_BASIC_TYPE bt
    on ea.type = bt.id
  where APP_MOD.APPLICATION_ID = APP.ID
    and charindex('RHUBARB', upper(APP.name)) > 0  /* Comment this line out for all APP */
    and APP.IS_ACTIVE = 1
    and MODU.ID = APP_MOD.MODULE_ID
    and ESP.ID  = modu.ESPACE_ID
    and ESP.IS_ACTIVE = 1
    and ENT.ESPACE_ID = modu.ESPACE_ID
    and ENT.IS_ACTIVE = 1
    and ENT.IS_SYSTEM = 0
    and ea.ENTITY_ID = Ent.id
  ORDER BY APP.NAME, ESP.NAME,
   ENT.Data_Kind DESC,
   ENT.NAME, ENT.PHYSICAL_TABLE_NAME, ea.ORDER_NUM

There were 2 earlier replies to this, that I cannot see??

Steve Pope wrote:

There were 2 earlier replies to this, that I cannot see??


Hi,

Most likely the posters have deleted their reply for some reason.

Regards,

Daniel

Hello Steve. The deleted comments were spam comments. 

Hi Steve,

you'll need to do some replacements and sub queries to translate for example:


bt50957770-1dd0-42bf-9fcb-c57953b87ce1*Entity5B57028B-F636-4BA3-93F9-C81CACBC5638

The first part is the SS_Key of ossys_Espace, so remove the bt and find the corresponding espace. you'll need the ID of the espace record as well for the second part, since that one is the SS_Key from ossys_Entity but you'll find duplicates unless you use the Espace_ID field as well.

All those combinations are ss_key fields, the first part is most like the identifier of the table you have to query, so BT = ossys_espace, Entity = ossys_entity. Not 100% sure, but it seems to look that way. Hope this helps you a bit on your way. Some don't have anything in front of the SS-key part but they seem to be from ossys_entity as well. you'll have to investigate a bit on that part..

Goodluck with it

Eduardo Jauch wrote:

Hello Steve. The deleted comments were spam comments. 

Thanks..


Daniël Kuhlmann wrote:

Steve Pope wrote:

There were 2 earlier replies to this, that I cannot see??


Hi,

Most likely the posters have deleted their reply for some reason.

Regards,

Daniel

Thanks.


Jeroen Bezemer wrote:

Hi Steve,

you'll need to do some replacements and sub queries to translate for example:


bt50957770-1dd0-42bf-9fcb-c57953b87ce1*Entity5B57028B-F636-4BA3-93F9-C81CACBC5638

The first part is the SS_Key of ossys_Espace, so remove the bt and find the corresponding espace. you'll need the ID of the espace record as well for the second part, since that one is the SS_Key from ossys_Entity but you'll find duplicates unless you use the Espace_ID field as well.

All those combinations are ss_key fields, the first part is most like the identifier of the table you have to query, so BT = ossys_espace, Entity = ossys_entity. Not 100% sure, but it seems to look that way. Hope this helps you a bit on your way. Some don't have anything in front of the SS-key part but they seem to be from ossys_entity as well. you'll have to investigate a bit on that part..

Goodluck with it

Thank you for your time.
Will park this for a while and go with what I have..
Certainly want to get back to this at some point though..