Question
Jaffa Brown · May 25

Script out a routine (stored procedure) name

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
00
2 0 4 75
Log in or sign up to continue

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

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()

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)

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.