Searching SQL History
Published 08 December 2022
The entire history of all of your queries can be searched using SQL History. To find a query, type some text and the results will be shown in the list below.
All words in the search text will have to be matched for a query to be classed as a result.
Wildcards are supported in the search text:
| Wildcard | Description | Example |
|---|---|---|
| * | matches 0 or more characters | proc* will match both proc and procedure |
| ? | matches exactly 1 of any character | an? will match both and and any |
Searches can also be limited to different properties of a query by using one of several prefixes:
| Prefix | Description | Example |
|---|---|---|
| name | Search only in the name of queries | name:fix |
| path | Search only in the file path of queries | path:useful |
| sql | Search only in the contents (SQL) of queries | sql:alter |
Advanced search
For more control over the filtering of results, you can use the advanced search section.
This allows filtering based on the different properties of a query/version. The filters can also be defined as prefixes in the search text.
| Filter | Description | Prefix | Example |
|---|---|---|---|
| Object type | Search everything, just queries, or just query versions. | type | type:query, type:version |
| Search period | Limit how far back the search extends. | date | date:[yyyyMMddHHmm TO yyyyMMddHHmm] |
| Server | Limit the results to queries connected to a particular server. | server | server:production.database.windows.net |
| Database | Limit the results to queries connected to a particular database. | database | database:AdventureWorks |
| State - Starred | Limit the results to queries that have been starred or not. | starred | starred:true, starred:false |
| State - Open | Limit the results to queries that are currently open/not open. | open | open:true, open:false |
These can be combined together to find very specific things in the SQL history:
type:query date:[202212010000 TO *] database:Adventureworks open:false starred:true
Additional search features
To search for a series of words that appear together in a query, put the words in double quotes (e.g. "create view").
By default, all words in the search text will have to be matched for a query to be classed as a result. This can be changed by using OR between the words (e.g. select OR alter).
Specific words can be excluded from results by using the NOT operator (e.g. alter NOT table).
Both the OR and NOT operators must be in upper case for them to be recognized as an operator.
Why am I not getting the results I expect?
Casing of both the words you are searching with and the query text you are searching for can have an effect on the results. To give the likeliest optimal results, compound words are broken up when searching. To do this, capital letters are treated as a word boundary.
For example, if the table CustomerOrders appears in a query, this will be broken into Customer, Orders and CustomerOrders when searching. Searching for any of these will get a result. If the CustomerOrders table was defined as Customerorders instead, searching for customer or orders will not find it.
The same word boundary logic is applied for the text being used in the search.
If you don't find what you expect to, try to change the search text slightly by lower casing it and splitting words apart.
