SQL Server 2012: T-SQL Code Snippets

  • Article

The snippets in SQL Server 2012 are essentially templates that can expedite building database statements.

Saleem Hakani

Imagine a series of commands you always use when creating a Trigger, Table, Stored Procedure or even a Select statement. Now imagine having all those commands established and ready to use. You can greatly reduce the amount of time and code you have to write using the new T-SQL code snippets in SQL Server 2012.

T-SQL snippets let you quickly build T-SQL statements without having to remember the commands or their syntax. You can use this feature to help reduce development time and increase productivity for your developers and DBAs.

Snippet templates in SQL Server 2012 are based on XML with predefined fields and values. When you use a T-SQL snippet, these fields are highlighted and you can tab through each field and change the values as required.

Snippets are categorized for ease of use. You can view and select various snippets based on the category. SQL Server 2012 introduces three types of snippets:

  • Default Snippets (or Expansion Snippets): These are code templates for various T-SQL commands you can quickly insert into your T-SQL code when creating tables, stored procedures, triggers and so on.
  • Surround Snippets: These are code templates that help you implement code constructs such as Begin End, If, While and so on.
  • Custom Snippets: You can create your own custom snippets that will appear with the snippet menu.

Create Custom Snippets

Let’s look at how to create a custom snippet and add it to the snippet menu. Creating and using a snippet is a three-step process:

  1. Create a snippet using XML
  2. Register the snippet in SQL Server Management Studio (SSMS)
  3. Invoke the snippet when using Query Editor

By default, all T-SQL snippets are stored in the following folder and saved as .snippet files:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033

Step 1. Create a T-SQL Snippet File with XML
Here’s a snippet you can use to write a Select statement for any table (it will also let you use a CASE statement for an equality check on a column):

CASE_END.SNIPPET File <?xml version="1.0" encoding="utf-8" ?> <CodeSnippets xmlns="https://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> <CodeSnippet Format="1.0.0"> <Header> <Title>Case-End</Title> <Description> Insert Case...End Construct. </Description> <Author> Saleem Hakani (Microsoft Corporation) </Author> <SnippetTypes> <SnippetType>Expansion</SnippetType> </SnippetTypes> </Header> <Snippet> <Code Language="SQL"> <![CDATA[ Select <Column_Name1>, <Column_Name2>, <Column_Name3>, <Column_Name4>= CASE <Column_Name4> WHEN '<value>' THEN '<Result>' WHEN '<value>' THEN '<Result>' WHEN '<value>' THEN '<Result>' WHEN '<value>' THEN '<Result>' ELSE 'Value not found' END, <Column_Name5>, <Column_Name6> From <Table_Name> Go ]> </Code> </Snippet> </CodeSnippet> </CodeSnippets>

Step 2. Register the Snippet with SSMS

Once you’ve created this file, use the Code Snippets Manager to register the snippet with SSMS. You can either add a new folder based on the snippet category or import individual snippets to the My Code Snippets folder.

To add a snippet folder:

  1. Launch SSMS
  2. Select Tools from the menu items and click Code Snippets Manager, which launches the Snippet Manager
  3. Click the "Add" button
  4. Browse to the folder containing CASE_END.Snippet file, and click the Select Folder button

The next step is to import the snippet in to SSMS:

  1. Launch SSMS
  2. Select Tools from the menu items and click Code Snippets Manager
  3. Click the Import button at the bottom
  4. Browse to the folder containing CASE_END.snippet file and select CASE_End.snippet file, then click the Open button

Step 3. Invoke or Insert a T-SQL Snippet from Query Editor

You now have a snippet called CASE_END that you can invoke from the query editor with the shortcut key by pressing CTRL + K + X. Then select the category folder in which you’ve stored the snippet. You could also right-click on the context menu in query editor and select Insert Snippet.

You can also invoke a snippet by right-clicking on the context menu in the query editor. This will present you with various Snippet Options.

Using these steps, you can create T-SQL code snippets and register them with SSMS. You can also create complex snippets of various regular tasks and make your life managing SQL Server much easier.

Snippet Solutions

Imagine you’re a developer or a DBA responsible for the security of your servers. You may have 500 logins in SQL Server, but you don’t know the server level roles to which these 500 logins are assigned. If you were to individually check each login’s properties, it would take hours or even days. Having an automated way to quickly check all server-level logins would reduce the time to code, as well as increase code accuracy and developer and DBA productivity.

Here’s a snippet that will let you quickly look at server-level logins and their server-level roles and permissions. This SecuritySPY snippet list identifies server-level logins and the roles to which they’re assigned:

<?xml version="1.0" encoding="utf-8" ?> <CodeSnippets xmlns="https://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> <CodeSnippet Format="1.0.0"> <Header> <Title>SQL_SecuritySPY - By Saleem Hakani (Microsoft Corporation)</Title> <Description> Shortcut for checking SQL Server Server Role Permissions </Description> <Author> Saleem Hakani (Microsoft Corporation) </Author> <SnippetTypes> <SnippetType>Expansion</SnippetType> </SnippetTypes> </Header> <Snippet> <Code Language="SQL"> <![CDATA[ --Author: Saleem Hakani (Microsoft Corporation) --Website: https://sqlcommunity.com Select 'Login Name'= Substring(upper(SUSER_SNAME(SID)),1,40), 'Login Create Date'=Convert(Varchar(24),CreateDate), 'System Admin' = Case SysAdmin When 1 then 'YES (VERIFY)' When 0 then 'NO' End, 'Security Admin' = Case SecurityAdmin When 1 then 'YES (VERIFY)' When 0 then 'NO' End, 'Server Admin' = Case ServerAdmin When 1 then 'YES (VERIFY)' When 0 then 'NO' End, 'Setup Admin' = Case SetupAdmin When 1 then 'YES (VERIFY)' When 0 then 'NO' End, 'Process Admin' = Case ProcessAdmin When 1 then 'YES (VERIFY)' When 0 then 'NO' End, 'Disk Admin' = Case DiskAdmin When 1 then 'YES (VERIFY)' When 0 then 'NO' End, 'Database Creator' = Case DBCreator When 1 then 'YES (VERIFY)' When 0 then 'NO' End from Master.Sys.SysLogins order by 3 desc Go ]> </Code> </Snippet> </CodeSnippet> </CodeSnippets>

Now you have a snippet called SecuritySPY. You can invoke this with the shortcut key from the query editor, as explained previously. You also can right-click on the context menu in query editor and select Insert Snippet.

Creating and using snippets can streamline your SQL Server management tasks. Having a handful of predefined commands at your disposal keeps you from having to do the same thing over and over again.

Greg Steen

Saleem Hakani is a principal architect at Microsoft with more than 18 years of experience. He has worked with SQL Server since 1992 and has worked on numerous large-scale Microsoft services as an engineer and architect over the last seven years, including Hotmail, Bing and MSN. Hakani leads the worldwide SQL Server Community for Microsoft employees, and is a technical presenter for various Microsoft events including TechReady, SQLFEST, SQL-SCHOOL and SQLPASS.

Related Content