Azure Management API to return SQL DB Query Text

Question

Hi everyone. I'm hoping someone can help. I'm building a process to query Azure SQL DB information from the Azure Management API. I have everything running well, and can query the API to get a massive amount of information about my Azure SQL DB, including the top query information, which includes execution count, execution time, CPU, and disk information and notably the query ID. However, I cannot find any API call that will return the query text for the query ID... there's an API for Azure SQL Managed Instance databases, but nothing for Azure SQL Databases.

So I'm reaching out to see if anyone has tried this before, and found an API that let's you pull back query text for a specific query ID, for an Azure SQL Database?

Thanks in advance,
RM.

Answer 1

In case you are willing to create your own API. See example here.

Below query is all you need.

SELECT qt.query_sql_text query_text FROM sys.query_store_query q JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id WHERE q.query_id= 957