Template Query Parameters in SSMS

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

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

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

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!