HELP... listing all procedures within a database

General
Enthusiast

HELP... listing all procedures within a database

I need to create a script that lists all the contents of procedures in our databases.  I can do it manually using BTEQ.. there must be a way to build the “show procedure” statement dynamically…  so that I can run with a scheduler

 

BTEQ method 

.SET WIDTH 2000

.export reset

.export report file=C:\Users\gabrielli.vincenzo\Documents\all_views_in_grp_cdm_report.out

Show procedure GRP_CDM_IMS.BCRIS_ORDER_Test;

Show procedure GRP_CDM_IMS.C06_PROCESS;

Show procedure GRP_CDM_IMS.MERGE_USP_MNS_NM1_ACCT_REF;

Show procedure GRP_CDM_IMS.MERGE_USP_MNS_NM1_ACCT_REF_EXP;

 

etc.. etc... etc...

.export reset

 

I can use this script to identify all the procedures in a database.. but how do I generate the Show procedure?

select * from dbc.tables

where databasename = 'GRP_CDM_PROD_ETL'

and tablekind = 'P'

  • list procedures VG
Tags (1)

Accepted Solutions
Junior Apprentice

Re: HELP... listing all procedures within a database

Try the following:

 

SELECT 'Show procedure '||databasename||'.'||TABLENAME||';'  (TITLE '')
FROM dbc.tablesv

WHERE databasename = 'GRP_CDM_PROD_ETL'
AND tablekind = 'P'
ORDER BY TABLENAME;

 

Writ ethis as a BTEQ script and schedule that script to run as required.

 

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
5 REPLIES
Junior Apprentice

Re: HELP... listing all procedures within a database

Try the following:

 

SELECT 'Show procedure '||databasename||'.'||TABLENAME||';'  (TITLE '')
FROM dbc.tablesv

WHERE databasename = 'GRP_CDM_PROD_ETL'
AND tablekind = 'P'
ORDER BY TABLENAME;

 

Writ ethis as a BTEQ script and schedule that script to run as required.

 

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Apprentice

Re: HELP... listing all procedures within a database

Also, note that I've used 'dbc.tablesv' and not 'dbc.tables'.

 

If you use 'dbc.tables' then you'll need to TRIM the Databasename and Tablename values in the SELECT list.

 

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: HELP... listing all procedures within a database

thank you Dave.. that works well. however I can't use BTEQ batch processing.. we are using a SAS environment schedual called process manager (lsf) and I need to call a USP to execute the show procedire.. how can I build a cript that will loop through the results?  I can put the results in a Volatile table and process each records.. will that work?

 

1 Show procedure GRP_CDM_PROD_ETL.USP_AGA_HUGS_VIEW;
2 Show procedure GRP_CDM_PROD_ETL.USP_ATLNTC_ORDRS_HIST;
3 Show procedure GRP_CDM_PROD_ETL.USP_ATLN_MDM_VIEWS;
4 Show procedure GRP_CDM_PROD_ETL.USP_BAKA_SLES;
5 Show procedure GRP_CDM_PROD_ETL.USP_FTTH_CALLS_VIEW;
6 Show procedure GRP_CDM_PROD_ETL.USP_FWFM_HIST;
7 Show procedure GRP_CDM_PROD_ETL.USP_HLD_ODRS_HIST;
8 Show procedure GRP_CDM_PROD_ETL.USP_INT_CNTRCT_TERM_HIST;
9 Show procedure GRP_CDM_PROD_ETL.USP_MOBLTY_VIEW;

Teradata Employee

Re: HELP... listing all procedures within a database

SHOW is not allowed within a stored procedure.

 

Use Dave's query in a two-step process in BTEQ, e.g.

.SET WIDTH 2000
.set format off
.set titledashes off

.export report file="C:\Users\gabrielli.vincenzo\Documents\show_stmts.tmp"
SELECT 'Show procedure '||databasename||'.'||TABLENAME||';'  (TITLE '')
FROM dbc.tablesv
WHERE databasename = 'GRP_CDM_PROD_ETL'
AND tablekind = 'P'
ORDER BY TABLENAME;
.export reset

.export report file="C:\Users\gabrielli.vincenzo\Documents\all_views_in_grp_cdm_report.out"
.run file="C:\Users\gabrielli.vincenzo\Documents\show_stmts.tmp"
.export reset

.os del "C:\Users\gabrielli.vincenzo\Documents\show_stmts.tmp"

Highlighted
Visitor

Re: HELP... listing all procedures within a database

thank you everyone... this information is exactly what I was looking for..
I will setup everything next week..  :)