How i get the Outsystems data Schema? 

How i get the Outsystems data Schema? 

  

I need to do something like this in Outsystems, i need the data dictionary


SELECT S.NAME AS [SCHEMA_TABLE], 
       T.NAME AS [TABLE NAME], 
       C.NAME AS [COLUMN NAME], 
       P.NAME AS [DATA TYPE], 
       CASE C.max_length 
         WHEN -1 THEN 'MAX' 
         ELSE CONVERT(VARCHAR, C.max_length) 
       END AS [SIZE], 
       CASE c.is_nullable 
         WHEN 0 THEN 'No Nulo' 
         ELSE 'Nulo' 
       END AS [Nullable], 
       CASE c.is_identity 
         WHEN 0 THEN '' 
         ELSE 'PK' 
       END AS [PK], 
       CASE 
         WHEN ( fk.object_id IS NULL ) THEN '' 
         ELSE 'FK' 
       END AS [FK], 
       Isnull(sep.value, '') [DescriptiON] 
FROM   sys.objects AS T 
       JOIN sys.columns AS C ON T.object_id = C.object_id 
       JOIN sys.schemas AS S ON T.schema_id = S.schema_id 
       JOIN sys.types AS P ON C.system_type_id = P.system_type_id 
       LEFT JOIN sys.extended_properties sep ON C.object_id = sep.major_id AND C.column_id = sep.minor_id AND sep.NAME = 'MS_DescriptiON' 
       LEFT JOIN (sys.foreign_keys fk 
                  INNER JOIN sys.foreign_key_columns fc ON ( fk.object_id = fc.constraint_object_id )) 
              ON ( ( fk.parent_object_id = C.object_id ) 
                   AND ( fc.parent_column_id = C.column_id ) ) 
WHERE  T.type_desc = 'USER_TABLE' 
       AND s.NAME <> 'dbo' 
ORDER  BY s.NAME, 
          T.NAME, 
          c.column_id;

You should use advance query.

From data tab -> structure, define structure containing all the attributes in your select clause. Use that structure in output structure of Advance query, write the query and test.

Hope this helps.

Solution

Hi Leidy, to get access to the meta data in Outsystems, take a look at the Module 'System' and look for the entities called 'Entities' (which holds the tables) and 'EntityAttributes' (which holds the columns).

If you need to query the data directly from the DB, you can look for the tables OSSYS_ENTITIES and OSSYS_ENTITY_ATTR

Solution

João Melo wrote:

Hi Leidy, to get access to the meta data in Outsystems, take a look at the Module 'System' and look for the entities called 'Entities' (which holds the tables) and 'EntityAttributes' (which holds the columns).

If you need to query the data directly from the DB, you can look for the tables OSSYS_ENTITIES and OSSYS_ENTITY_ATTR

thanks a lot