Create custom T-SQL code snippets with Azure Data Studio


By:   |   Updated: 2018-12-12   |   Comments (1)   |   Related: More > Database Administration

Problem

If you work with SQL Server, it is very likely you have a lot of custom scripts you execute frequently. Maybe you have stored them in a folder and you open them manually as you need them or have a custom solution to load them when you load your favorite IDE. If you already have your IDE open and want to use a block of T-SQL code without opening your script files or you need to access them quickly, you need a way to load pieces of code into the IDE, this is where code snippets come into play.

Solution

Code Snippets, are blocks of reusable code that can be loaded into your work environment via a menu or a combination of hotkeys. There are several applications where you can use snippets (SSMS, VS, SSDT, Azure Data Studio to name some). Each application has a lot of built-in snippets with common code sets that you can use by default, but in this tip, we will learn how to load frequently used T-SQL code via snippets as needed.  We will use Azure Data Studio IDE for this tip.

Loading Default Snippets in Azure Data Studio

If you have not used the default snippets yet, the following is the way to load them in an open document.

In a new query window, just type SQL and the list will open, after that you just must navigate with the UP/DOWN arrows to locate the snippet you want to load.

Azure Data Studio snippet 1

Select the item you want to load, and press ENTER, and the code snippet will load into the window:

Azure Data Studio snippet 2

Then just must replace the default values with the values you want.

Creating Custom Code Snippets

Go to View > Command Palette…

Azure Data Studio snippet 3

In the new open window type snippet, and select Preferences: Configure User Snippets

Azure Data Studio snippet 4

Type or locate SQL and click on it:

Azure Data Studio snippet 5

If you don’t have custom snippets defined, a new sql.json window will open with commented code that you can edit:

Azure Data Studio snippet 6

The basic template to add a new snippet is:

"<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

Our first snippet will look like this:

{
"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:

Azure Data Studio snippet 7

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:

Azure Data Studio snippet 8

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.

Testing the Custom Code Snippets in Azure Data Studio

As with the default snippets, just open a new query window and type sql, the 2 new snippets will show in the list:

Azure Data Studio snippet 9

Select one of them to load into your query window:

Azure Data Studio snippet 10

We can type sql again to load the second one:

Azure Data Studio snippet 11

We can see the code loaded in our environment:

Azure Data Studio snippet 12

Advanced Options for Code Snippets

In the previous two examples, we showed some basic options for the snippets, but you can customize them even more. I will show you some other options you can make.

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:

Azure Data Studio snippet 13

We can see that the default value of 100 is in place and we can choose between 2 values of PERCENT and ROWS.

For our last example we will use 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:

Azure Data Studio snippet 14

The first option is to select the dbcc command to run with the checkdb as default:

Azure Data Studio snippet 15

Then we can put the database name to check:

Azure Data Studio snippet 16

If we press Tab again, we can select if we want to include WITH NO_INFOMSGS or not:

Azure Data Studio snippet 17

And this will be the result:

Azure Data Studio snippet 18

You can load any number of custom T-SQL queries you use frequently using Azure Operations Studio.

Next Steps
  • You can enforce programming conventions in your development team by using snippets (for example: author, date of creation, changes done, object naming, or datatypes to use).
  • More complex procedures can be added as well.
  • Check Azure data studio official documentation here.
  • Check the official Visual Studio Code documentation for Snippets documentation here.
  • If you prefer SSMS, you can check this tip on how to load custom snippets here.


Last Updated: 2018-12-12


get scripts

next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, May 10, 2019 - 4:13:31 PM - Shawn Fumo Back To Top

Thanks, this was helpful! I want to point out that Settings has a configuration in Editor / Suggestions called "Suggest: Snippets Prevent Quick Suggestions". While I'm sure there's a reason it is checked to start with, I found it much more helpful to have it off. This way I can actually get auto-completion for table names during the snippet. Otherwise a snippet for something like "select * from ${1} where ${1}_id = ${2}" isn't very useful if you have very long table names or aren't sure on the exact name off the top of your head.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools