Get a list of cached queries and their texts

Article

Eduard Lebedyuk · Mar 4, 2021 1m read

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:

Discussion (4)1

Lucas Fernandes · Jul 7, 2021

At Caché I usually use it:

SELECT Routine, Query FROM %Library.SQLCatalog_SQLCachedQueryInfo()

Is there a difference?

4 1

Eduard Lebedyuk · Jul 7, 2021

I didn't know about this query. Awesome!

1 0

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

Chad Severtson · Jun 14

I use the following to get the full query.

SELECT Location, RuntimeLocation, Statement->Statement 
FROM INFORMATION_SCHEMA.STATEMENT_LOCATIONS 
WHERE Location  = ?
OR RunTimeLocation = ?

0 0