Create a script with Template Explorer in SSMS.

Template Explorer contains lots of SQL Example Scripts maybe you want to create.

Situation

Are you googling to find the example code for SQL Server scripts?

Here's Template Explorer what you can get quickly.

Open Template Browser

Select "View" in the menu and Select "Template Explorer".

Now you can see Template Browser on the Right pain.

Open a template

Backup script

If you want to create a backup script, then Select "Backup > Backup Database".

Then you will get the script as below.

-- ===========================
-- Backup Database Template
-- ===========================
BACKUP DATABASE <Database_Name, sysname, Database_Name> 
	TO  DISK = N'<Backup_Path,,C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\><Database_Name, sysname, Database_Name>.bak' 
WITH 
	NOFORMAT, 
	COMPRESSION,
	NOINIT,  
	NAME = N'<Database_Name, sysname, Database_Name>-Full Database Backup', 
	SKIP, 
	STATS = 10;
GO

Add key script

If you want to add key(constraint), then Select "Table > Add Key"

Then you will get the script as below.

-- =============================================
-- Add Key template
--
-- This template creates a table, then it  
-- adds a PRIMARY KEY constraint to the table
-- =============================================
USE <database, sysname, AdventureWorks>
GO

IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL
  DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
	<columns_in_primary_key, , column1>      int      NOT NULL, 
	column2      char(8)  NOT NULL
)
GO

-- Add a new PRIMARY KEY CONSTRAINT to the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
  ADD CONSTRAINT <constraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , column1>)
GO

Where is it?

It's possible to edit the existing template or to create a new one.

But, where is it?

Select "Edit" on Template Browser

Now, Select "Open Containing Folder".

Now, they are here.

In general, the location will be "C:\Users\[<User Name>]\AppData\Roaming\Microsoft\SQL Server Management Studio\[<SSMS Major Version>]\Templates\Sql"

References

Template Explorer

Sunsick's Tech Blog

Friday, 23 October 2020

[SQL Server] Make the SQL Life easier with Template Explorer in SSMS