New post
Article
Eduard Lebedyuk · Mar 4, 2021 1m read

Get a list of cached queries and their texts

#Code Snippet #SQL #InterSystems IRIS

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 464
Discussion (3)1
Log in or sign up to continue
Lucas Fernandes · Jul 7, 2021

At Caché I usually use it:

SELECT Routine, Query FROM %Library.SQLCatalog_SQLCachedQueryInfo()

 Is there a difference?  

4 1
Lucas Fernandes · Jul 7, 2021

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.

2 1