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: