Article
Eduard Lebedyuk · Mar 4, 2021 1m read

Get a list of cached queries and their texts

Recently I wanted to get a list of all cached queries and their texts. Here's how to do that.

First create an SQL Procedure returning Cache Query text from a Cached Query routine name:

Class test.CQ
{

/// SELECT test.CQ_GetText()
ClassMethod GetText(routine As %String) As %String [ CodeMode = expression, SqlProc ]
{
##class(%SQLCatalog).GetCachedQueryInfo(routine)
}

}

And after that you can execute this query:

SELECT Routine, test.CQ_GetText(Routine)
FROM %SQL_Manager.CachedQueryTree()

And get a list of Cached Queries:

4
0 431
Discussion (3)1
Log in or sign up to continue

At Caché I usually use it:

SELECT Routine, Query FROM %Library.SQLCatalog_SQLCachedQueryInfo()

 Is there a difference?  

I also don't know the %SQL_Manager.CachedQueryTree() and I didn't find it in the Doc. I don't know if there's a difference.