Customize statement templates

You can customize SQL statement templates by configuring general script options and editing templates for CRUD procedures.

To configure and edit templates

1. Navigate to Tools > Options.

2. Navigate to Generate Scripts and select one of the following sections:

• To configure general script options, select General, and then, in the dropdown list at the top of the dialog, select Generate Script As.

Tip

You can use the search box at the top of the dialog to find specific script options.

• To configure and edit CRUD templates, select CRUD, and then choose General or a specific template type: Select, Insert, Update, or Delete.

3. Make your changes and click OK.

General options

The following table describes the general template configuration options for the Generate Script As command.

Option name	Group	Description	Default state
Check for object existence	Common	Verifies whether the target database already contains an object before generating a creation script.	Off
Enclose identifiers within square brackets	Common	Wraps all database object identifiers in square brackets ([]) to ensure compatibility with reserved keywords and special characters.	Off
Generate comments in the script	Common	Includes descriptive comments in the generated script for better readability and traceability.	On
Script authorization	Common	Adds AUTHORIZATION clauses for database objects where applicable.	On
Script collation	Common	Includes collation information for columns and database-level objects.	Off
Script CREATE DATABASE	Common	Adds a CREATE DATABASE statement at the beginning of the generated script.	Off
Script extended properties	Common	Includes extended properties associated with database objects.	On
Script logins	Common	Scripts all logins defined on the server, including their properties.	Off
Script security permissions	Common	Scripts object-level and schema-level permissions.	Off
Script statistics	Common	Includes index and column statistics in the generated script.	Off
Script USE DATABASE	Common	Adds a USE [database_name] statement at the beginning of the script to specify the target database context.	On
Specify object name with its schema	Common	Prefixes all object names with their corresponding schema name.	On
Decrypt encrypted objects	Tables/Views	Decrypts encrypted SQL objects so that their definitions can be scripted for comparison and synchronization.	Off
Script change tracking	Tables/Views	Includes CHANGE_TRACKING settings and clauses when generating scripts for tables.	Off
Script check constraints	Tables/Views	Includes CHECK constraints defined on table columns.	On
Script DATA_COMPRESSION for indexes, primary and unique constraints	Tables/Views	Includes DATA_COMPRESSION settings for indexes and constraints in the script.	Off
Script default constraints	Tables/Views	Includes column default constraints and schema defaults in the script.	On
Script DML triggers	Tables/Views	Scripts CREATE TRIGGER statements for DML triggers defined on tables.	On
Script foreign keys	Tables/Views	Scripts CREATE statements for foreign key constraints.	On
Script full-text search	Tables/Views	Scripts full-text catalogs, stoplists, and indexes.	Off
Script indexes	Tables/Views	Scripts CREATE statements for table indexes.	On
Script primary keys	Tables/Views	Scripts CREATE statements for primary keys on tables.	On
Script sensitivity classification	Tables/Views	Includes sensitivity classification metadata for columns where defined.	Off
Script unique keys	Tables/Views	Scripts CREATE statements for unique constraints and indexes.	On
Include column names into INSERT statements	Data	Generates INSERT statements with explicit column name lists.	On

CRUD options

The following table describes configuration options for CRUD templates.

Option name	Description
General	Specifies which CRUD procedures to include in the generated SQL scripts. You can choose to include SELECT, INSERT, UPDATE, and DELETE statements. Also defines how columns are ordered in generated statements: by their ordinal position or alphabetically.
Select	Enables you to edit the SELECT statements template.
Insert	Enables you to edit the INSERT statement template.
Update	Enables you to edit the UPDATE statement template.
Delete	Enables you to edit the DELETE statement template.

Snippet placeholders

In the script templates, you can use predefined snippet placeholders to automatically insert object-specific values into generated SQL code. These placeholders act as variables that dbForge Studio for SQL Server replaces with actual metadata (for example, table names, column lists, or schema names) when creating a stored procedure.

The following tables describe the snippet placeholders used in templates.

General placeholders

Placeholder	Description
$table$	Specifies the name of the table for which you generate the CRUD procedures.
$schema$	Specifies the name of the schema.
$col.Name$	Specifies the name of a column in the table.
$col.AsParameter$	Defines the name of the procedure parameter based on the column name.
$col.Type$	Specifies the data type of the column.
$parameters$	Defines parameters based on $col.AsParameter$ and $col.Type$, separated by a comma (,) and a line feed (\n).
$col.WhereName$	Checks the column’s data type. If the type is geography or geometry, adds an STEquals($col.AsParameter$) comparison.
$col.WhereAsParameter$	Converts the column name to a procedure parameter. If the data type is geography or geometry, the value 1 is used instead.
format="id"	Specifies name formatting. If a name is quoted, it is quoted again. For example, 'id' becomes ''id''.

SELECT template

Placeholder	Description
$procedure$	Specifies the name of the procedure. For example, usp_$table$_Select.
$parameters$	Defines the procedure parameters only for the PRIMARY KEY columns.
$columns$	Lists column names (specified in $col.Name$) separated by “,” (comma).
$where$	Defines the selection conditions combined with the AND operator. If Return all data if input parameters are null is selected, the following condition is applied to primary key columns: ($col.WhereName$ = $col.WhereAsParameter$ OR $col.AsParameter$ IS NULL) If not selected, the condition is: $col.WhereName$ = $col.WhereAsParameter$ In both cases, the condition applies only to primary key columns.

INSERT template

Placeholder	Description
$procedure$	Defines the name of the procedure. For example, usp_$table$_Insert.
$columns$	Lists table columns, excluding IDENTITY and GENERATED ALWAYS.
$values$	States inserted data $col.AsParameter$ separated by “,” (comma) for all the columns, except IDENTITY and GENERATED ALWAYS.
$where$	Determines the list of selection conditions combined with the AND operator. If Return inserted row is selected and if the table has the IDENTITY and GENERATED ALWAYS columns, then the $col.Name$ = SCOPE_IDENTITY() condition will be available only for the IDENTITY columns. Otherwise, $col.WhereName$ = $col.WhereAsParameter$ will be available for all the columns, except GENERATED ALWAYS.

DELETE template

Placeholder	Description
$procedure$	Specifies the name of the procedure. For example, usp_$table$_Delete.
$parameters$	Defines the procedure parameters only for the PRIMARY KEY columns.
$where$	States the deletion conditions $col.WhereName$ = $col.WhereAsParameter$ combined with the AND operator only for the PRIMARY KEY columns.

UPDATE template

Placeholder	Description
$procedure$	Defines the name of the procedure. For example, usp_$table$_Update.
$parameters$	Specifies the procedure parameters for all the columns, except GENERATED ALWAYS.
$columns$	States the columns list of the table, except IDENTITY and GENERATED ALWAYS.
$assignments$	Determines the list of updated data $col.WhereName$ = $col.WhereAsParameter$ combined with the AND operator only for the PRIMARY KEY columns.