I have both permanent and template tables in our repositories. Is there a way to list out all the permanent tables from the BODS metadata? I tried ALVW_TABLEINFO and AL_SCHEMA but it returns both permanent and template tables.
pillaibinu23@ (BOB member since 2014-06-14)
The column AL_SCHEMA.TABLE_TYPE might have the perm/temp information.
eganjp 
(BOB member since 2007-09-12)
TABLE_TYPE only contains the value ‘TABLE’. However, i tried AL_ATTR table where there is an attribute Loader_Is_Template_Table. This is giving the required information. I am using the below SQL for this,
SELECT X.NAME, X.OWNER, X.DATASTORE,X.TABLE_TYPE,Y.* FROM
AL_SCHEMA X LEFT OUTER JOIN AL_ATTR Y
ON (X.OBJECT_KEY = Y.PARENT_OBJID)
WHERE ATTR_NAME = ‘Loader_Is_Template_Table’
AND UPPER(ATTR_VALUE) = ‘NO’
AND PARENT_OBJ_TYPE = ‘7’
Would request to let me know if there are some suggestions or is there some other way to achieve the purpose?
pillaibinu23@ (BOB member since 2014-06-14)
It sounds like that is a reasonable solution.
eganjp (BOB member since 2007-09-12)