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:
.png)
At Caché I usually use it:
SELECT Routine, Query FROM %Library.SQLCatalog_SQLCachedQueryInfo()Is there a difference?
I didn't know about this query. Awesome!
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.
I use the following to get the full query.