Script out a routine (stored procedure) name

Question

Question

Jaffa Brown · May 25, 2021

#Caché

Afternoon all,

Is there a way (at SQL level) to script out the definition of a Cache stored procedure from within SQL?

In SQL Server, I would do...

Select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'my_sproc_name'

But cant find anyway to do this in cache.

Note, its an old version of Cache, so "INFORMATION_SCHEMA" is not available.

I am trying to see the SQL definition in the sproc from SQL itself.

Thanks all

Product version: Caché 2014.1

Discussion (4)2

Log in or sign up to continue

Robert Cemper · May 25, 2021

Ok. this is not MS-SQL.
Connect with your Studio to the correct Namespace and print out the related ClassMethod.
This would most likely be some code in ObjectScript and not look like SQL

0 0

Julius Kavay · May 25, 2021

If I got you correctly... for IRIS (and newer Cache Versions) you can use

select * from  INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME='...'

and for older Cache versions try

select * from %Dictionary.CompiledMethod where SqlProc=1 and Name='...'

(but be patient, this takes some time)

0 0

score 0 · Answer 1 · 2021-05-25T13:15:15-04:00

How old are you talking about? In any case, you could use old stored procedures provided by Cache to pull the data. There will be under %SQL.Manager.Catalog, eg. %SQL.Manager.Catalog.Procedures()

score 0 · Answer 2 · 2021-05-26T16:28:38-04:00

You might also find some relief by querying %Dictionary.MethodDefinition WHERE SQLProc=1 or %Dictionary.QueryDefinition. If it's a really old version you might look in %Library schema.