Template Query Parameters in SSMS

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

 

Have you ever thought about what this button will do for you?

Template Query Parameters in SSMS_1

This is an easy way to replace parameter values in a script template. If you have large scripts that you need to change parameters (one or many) and you want to use a more safe way to replace them than “Find & Replace” and be sure that you do not miss any this is the way.

In your script template you simply replace your parameters with

<parameter_name, data_type, default_value>

You can have one or many of these template parameters in your script.

When you open your script template you simply press the “Specify values for Template Parameters”-button or you can press Ctrl-Shift-M and you get the wizard for specifying values.

Example:

Alter database <database_name, sysname,database_name>  Set recovery SIMPLE

Template Query Parameters in SSMS_2

Alter database test Set recovery SIMPLE

 

An example for a predefined script creating a login, user and adding to DB-roles in database TEST:

USE [master]
GO
CREATE LOGIN [<new_user,username,username>] WITH PASSWORD=N'<password,string,password’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [<db_name,sysname,db_name>]
GO
CREATE USER [<new_user,username,username>] FOR LOGIN [<new_user,username,username>]
ALTER ROLE [db_datareader] ADD MEMBER  [<new_user,username,username>]
ALTER ROLE [db_datawriter] ADD MEMBER  [<new_user,username,username>]
GO

Template Query Parameters in SSMS_3

USE [master]
GO
CREATE LOGIN [test2] WITH PASSWORD=N’password’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [test]
GO
CREATE USER [test2] FOR LOGIN [test2]
ALTER ROLE [db_datareader] ADD MEMBER  [test2]
ALTER ROLE [db_datawriter] ADD MEMBER  [test2]
GO

 

 

If you try to run the script and forget to replace parameters, the script will fail.

Good Luck!