.SET FOLDLINE OFF

.SET ERROROUT STDOUT
.SET WIDTH 254
.SET TITLEDASHES OFF 
.OS IF EXIST sp_nme.txt            DEL sp_nme.txt
.OS IF EXIST sp_ddl.txt            DEL sp_ddl.txt
.OS IF EXIST Object_Dependency.txt DEL Object_Dependency.txt

.RUN FILE logon.txt


SEL 'EXIST' 
FROM DBC.Tables
WHERE DatabaseName = 'EDWPRODTEMP'
AND TableName IN ('SP_DDL');

.IF ACTIVITYCOUNT > 0 THEN DROP TABLE EDWPRODTEMP.SP_DDL;

SEL 'EXIST' 
FROM DBC.Tables
WHERE DatabaseName = 'EDWPRODTEMP'
AND TableName IN ('SP_NME');

.IF ACTIVITYCOUNT > 0 THEN DROP TABLE EDWPRODTEMP.SP_NME;

CREATE MULTISET TABLE EDWPRODTEMP.SP_DDL (
TS TIMESTAMP(6)
,LineDetail VARCHAR(255));

CREATE MULTISET TABLE EDWPRODTEMP.SP_NME (
ProcDB VARCHAR(30)
,ProcNme VARCHAR(30)
,LineDetail VARCHAR(255));



INSERT INTO EDWPRODTEMP.SP_NME 
SELECT 
DatabaseName 
,TableName  
,'SHOW PROCEDURE ' || TRIM(DatabaseName) || '.' || TRIM(TableName) || ';' (TITLE '')
FROM DBC.Tables 
WHERE DatabaseName = 'SYSDBA'
AND TableKind = 'P';



.SET TITLEDASHES OFF 
.EXPORT REPORT FILE = sp_nme.txt


SELECT 
'SHOW PROCEDURE ' || TRIM(DatabaseName) || '.' || TRIM(TableName) || ';' (TITLE '')
FROM DBC.Tables 
WHERE DatabaseName = 'SYSDBA'
AND TableKind = 'P';



.EXPORT RESET 

.EXPORT REPORT FILE sp_ddl.txt
.RUN FILE sp_nme.txt
.EXPORT RESET 


.IMPORT VARTEXT FILE = sp_ddl.txt
.REPEAT *
USING LineDetail (VARCHAR(255))

INSERT INTO EDWPRODTEMP.SP_DDL (TS, LineDetail)
VALUES (CURRENT_TIMESTAMP(6), :LineDetail);

.SET TITLEDASHES ON

.EXPORT REPORT FILE Object_Dependency.txt


SEL 
 CAST((CASE WHEN ProcDb  IS NULL THEN '              - do -                               ' ELSE ProcDB END) AS CHAR(30))  AS ProcDB
,CAST((CASE WHEN ProcNme IS NULL THEN '              - do -                               ' ELSE ProcNme END) AS CHAR(30))  AS ProcNme
,DatabaseName
,TableName AS ObjectName
,CASE WHEN TableKind = 'P' THEN 'PROCEDURE'
      WHEN TableKind = 'V' THEN 'VIEW'
      WHEN TableKind = 'T' THEN 'TABLE' End AS ObjectType

FROM (SEL 
      DatabaseName
     ,TableName
     ,TableKind
     ,TS
     ,LineDetail
     FROM EDWPRODTEMP.SP_DDL, DBC.Tables 
     WHERE POSITION(TRIM(DatabaseName) IN LineDetail) > 0 
     AND POSITION(TRIM(TableName) IN LineDetail) > 0 
     AND TableKind IN ('T', 'V','P' )
     AND TRIM(LineDetail) NOT LIKE '--%'  ) AS T 

LEFT JOIN EDWPRODTEMP.SP_NME 
ON DatabaseName = ProcDB
AND TableName = ProcNme
ORDER BY TS ASC;


.EXPORT RESET

DROP TABLE EDWPRODTEMP.SP_DDL; 
DROP TABLE EDWPRODTEMP.SP_NME;

.logoff
.quit