To get started, follow these steps to find and use the default snippets in Azure Data Studio. In a new query window, just type SQL and the list will open. Then, navigate with the UP/DOWN arrows to find the snippet you want to load.
Select the item you want to load, and press ENTER, and the code snippet will load into the window:
Then just must replace the default values with the values you want.
Creating Custom Code Snippets
Go to the View menu and choose Command Palette.
In the new open window type snippet, and select Preferences: Configure User Snippets
Type or locate SQL and click on it:
If you don’t have custom snippets defined, a new sql.json window will open with commented code that you can edit:
You can create a new snippet using the following template.:
"<name of the snippet>": {
"prefix":"<sqlNameOfthePrefix>",
"body":[
"<your T-SQL code line 1 ${1:DefaultValue1}>",
"<your T-SQL code line 2 ${2:DefaultValue2}>"
"<your T-SQL code line N>"
],
"description":"<Snippet description>"
}
You can add the number of snippets you want separating each by commas.For this tip, let's add 2 custom snippets. For the first snippet we will update all statistics on any given table:
UPDATE STATISTICS <table name>; GO
The first snippet is formatted as follows:-
{
"Update table statistics": {
"prefix":"sqlUpdateStatistics",
"body":[
"UPDATE STATISTICS ${1:SchemaName}.${2:TableName};",
"GO "
],
"description":"Updates all the statistics on a table"
}
}Note that the variables are Schema Name and Table name, indicated by a ${n:defaultValue} so they are highlighted when you insert it in your code.
And the SQL.JSON file will look like this at this point:
We will add a second snippet, this time to verify the status of a running Extended Event. The T-SQL code is this:
SELECT ES.name, iif(RS.name IS NULL, 0, 1) AS running FROM sys.dm_xe_sessions RS RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name WHERE es.name = '<YOUR XE SESSION NAME>'
So, the code for our second snippet will be this:
"Extended Event Status": {
"prefix":"sqlXEStatus",
"body":[
"SELECT ",
"ES.name, ",
"iif(RS.name IS NULL, 0, 1) AS running ",
"FROM sys.dm_xe_sessions RS ",
"RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name ",
"WHERE es.name = '%${1}%'"
],
"description":"Show the status for any extended event"
}For this one we did not define a default value for text to search, so this will check all your XE sessions.
Our SQL.JSON code looks like this:
And this is the code:
{
"Update table statistics": {
"prefix": "sqlUpdateStatistics",
"body":[
"UPDATE STATISTICS ${1:SchemaName}.${2:TableName};",
"GO "
],
"description": "Updates all the statistics on a table"
},
"Extended Event Status": {
"prefix": "sqlXEStatus",
"body":[
"SELECT ",
"ES.name, ",
"iif(RS.name IS NULL, 0, 1) AS running ",
"FROM sys.dm_xe_sessions RS ",
"RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name ",
"WHERE es.name = '%${1}%'"
],
"description": "Show the status for any extended event"
}
}Now, just save it and it is ready to test in a new query window.
Select one of them to load into your query window:
We can type sql again to load the second one:
We can see the code loaded in our environment:
Advanced Code Snippets
While the previous examples showcased basic snippet options, there are additional ways to customize them, but you can customize them even more.
Let’s go back to our first custom snippet. The table statistics update with the defaults, but you can customize the code to allow sample size and by percent or rows. Let’s go back to the SQL.JSON file and replace the update table statistics snippet with this code:
"Update table statistics": {
"prefix":"sqlUpdateStatistics",
"body":[
"UPDATE STATISTICS ${1:<Schema>}.${2:<Table>} WITH SAMPLE ${3:100} ${4|PERCENT,ROWS|};",
"GO "
],
"description":"Updates all the statistics on a table"
}The ${3:100} variable puts the value of 100 by default, and the ${4|PERCENT,ROWS|} allows you to choose between the two values of PERCENT or ROWS when inserted, to specify more values, you must separate it by commas.
If we save the updated file and then load in a new query window and insert the snippet again, we can see how these options work:
We can see that the default value of 100 is in place and we can choose between 2 values of PERCENT and ROWS.
Our final example will demonstrate the database consistency check (DBCC) function. Open the SQL.JSON file and add the following snippet to the end:
"Database Consistency check": {
"prefix":"sqlDBCC",
"body":[
"DBCC ${1|CHECKDB,CHECKALLOC,CHECKCATALOG|}(${2:DBNAME}) ${3| ,WITH NO_INFOMSGS|};",
"GO "
],
"description":"Perform Database consistency check tasks"
}The first variable ${1|CHECKDB,CHECKALLOC,CHECKCATALOG|} will allow you to choose between CHECKDB, CHECKALLOC and CHECKCATALOG. The second variable ${2:DBNAME} is to enter the database name to check. The third variable will let you choose if you want the WITH NO_INFOMSGS option or an empty string at the end.
Your code with the 3 snippets should look like this:
{
"Update table statistics": {
"prefix": "sqlUpdateStatistics",
"body":[
"UPDATE STATISTICS ${1:<Schema>}.${2:<Table>} WITH SAMPLE ${3:100} ${4|PERCENT,ROWS|};",
"GO "
],
"description": "Updates all the statistics on a table"
},
"Extended Event Status": {
"prefix": "sqlXEStatus",
"body":[
"SELECT ",
"ES.name, ",
"iif(RS.name IS NULL, 0, 1) AS running ",
"FROM sys.dm_xe_sessions RS ",
"RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name ",
"WHERE es.name = '%${1}%'"
],
"description": "Show the status for any extended event"
},
"Database Consistency check": {
"prefix": "sqlDBCC",
"body":[
"DBCC ${1|CHECKDB,CHECKALLOC,CHECKCATALOG|}(${2:DBNAME}) ${3| ,WITH NO_INFOMSGS|};",
"GO "
],
"description": "Perform Database consistency check tasks"
}
}Save the SQL.JSON file and open a new query window to test it.
If you type dbcc the snippet will also load:
The first option is to select the dbcc command to run with the checkdb as default:
Then we can type the database name to check:
If we press Tab again, we can select if we want to include WITH NO_INFOMSGS or not:
And this will be the result:
You can easily load as many custom T-SQL queries as you need in Azure Data Studio.
Conclusion
Adding custom T-SQL code snippets to your work can really boost your productivity. It makes repetitive tasks easier and keeps your coding standards the same. This saves time and helps your team work better together.
As you make snippets for your needs, you might find new ways to manage your databases. Snippets can make SQL Server work easier. So, use them to improve your work and make your job more efficient.