How to generate the DDL and DML statements for database objects

In dbForge Studio for SQL Server, you can generate the DDL, DML, CRUD, and EXECUTE statements based on existing database objects.

Generate DDL statements

In dbForge Studio for SQL Server, you can generate DDL statements for the following database objects.

ClickClick to open the list of supported objects
  • Aggregate function (CREATE, DROP, CREATE OR ALTER, DROP and CREATE)
  • Application role (CREATE, DROP, DROP and CREATE)
  • Assembly (CREATE, DROP, DROP and CREATE)
  • Asymmetric key (CREATE, DROP, DROP and CREATE)
  • Broker priority (CREATE, DROP, DROP and CREATE, ALTER)
  • Certificate (CREATE, DROP, DROP and CREATE)
  • Column Encryption Key (CREATE, DROP, DROP and CREATE)
  • Column Master Key (CREATE, DROP, DROP and CREATE)
  • Contract (CREATE, DROP, DROP and CREATE, ALTER)
  • Constraints (CREATE, DROP and CREATE, DROP)
  • Database (CREATE, DROP and CREATE, DROP)
  • Database role (CREATE, DROP, DROP and CREATE)
  • Database trigger (CREATE, DROP, CREATE OR ALTER, DROP and CREATE)
  • Default (CREATE, DROP, DROP and CREATE)
  • DML Triggers (CREATE, DROP, ALTER, CREATE OR ALTER, DROP and CREATE)
  • Event notification (CREATE, DROP and CREATE, DROP)
  • External Data Source (CREATE, DROP, DROP and CREATE)
  • Full-text catalog (CREATE, DROP, DROP and CREATE)
  • Full-text stoplist (CREATE, DROP, DROP and CREATE)
  • Indexes (CREATE, DROP and CREATE, DROP)
  • Message type (CREATE, DROP, DROP and CREATE, ALTER)
  • Partition Function (CREATE, DROP and CREATE, DROP)
  • Partition Scheme (CREATE, DROP and CREATE, DROP)
  • Procedure (CREATE, DROP, ALTER, CREATE OR ALTER, DROP and CREATE)
  • Queue (CREATE, DROP, DROP and CREATE, ALTER)
  • Remote service binding (CREATE, DROP, DROP and CREATE, ALTER)
  • Route (CREATE, DROP, DROP and CREATE, ALTER)
  • Rule (CREATE, DROP, DROP and CREATE)
  • Scalar-valued function (CREATE, DROP, ALTER, CREATE OR ALTER, DROP and CREATE)
  • Schema (CREATE, DROP, DROP and CREATE)
  • Search Property List (CREATE, DROP and CREATE, DROP)
  • Sequence (CREATE, DROP, DROP and CREATE)
  • Service (CREATE, DROP, DROP and CREATE, ALTER)
  • Statistics (CREATE, DROP and CREATE, DROP)
  • Symmetric key (CREATE, DROP, DROP and CREATE)
  • Synonym (CREATE, DROP, DROP and CREATE)
  • Table (CREATE, DROP and CREATE, DROP)
  • Table-valued function (CREATE, DROP, ALTER, CREATE OR ALTER, DROP and CREATE)
  • User (CREATE, DROP, DROP and CREATE)
  • User-defined data type (CREATE, DROP, DROP and CREATE)
  • User-defined table type (CREATE, DROP, DROP and CREATE)
  • View (CREATE, DROP, ALTER, CREATE OR ALTER, DROP and CREATE)
  • XML schema collection (CREATE, DROP, DROP and CREATE)

You can generate DDL statements either from Database Explorer or Object Viewer.

Generate DDL statements from Database Explorer

1. In Database Explorer, right-click the required database object and point to Generate Script As.

2. Hover over the DDL statement you want to generate.

3. Select one of the following options:

  • To New SQL Window: This will open the statement in a new SQL window in dbForge Studio for SQL Server
  • To File: This will save the statement as a SQL file
  • To clipboard: This will copy the statement to the clipboard

Generate script

Generate DDL statements from Object Viewer

1. To open Object Viewer, select it from the View menu, or simply press F7.

Object Viewer

2. To access the database object you need, simply select it from the Database Explorer pane. It will automatically appear in the Object Viewer. Alternatively, you can directly navigate to the desired object within the Object Viewer.

3. To generate a script for the database object, right-click it in the Object Viewer grid and select Generate Script As. This will provide you with the same options as those in the Database Explorer pane.

DDL for a view

Generate the DML statements

In dbForge Studio for SQL Server, you can generate DML statements for:

  • Aggregate function (SELECT)
  • Table (SELECT, INSERT, UPDATE, DELETE)
  • Table-valued function (SELECT)
  • Scalar-valued function (SELECT)
  • View (SELECT, INSERT, UPDATE, DELETE)

Generate DML statements from Database Explorer

1. Right-click the required database object and point to Generate Script As.

2. Hover over the required DML statement.

3. Select one of the following options:

  • To New SQL Window: This will open the statement in a new SQL window
  • To File: This will save the statement as a SQL file
  • To clipboard: This will copy the statement to the clipboard

DML for a table

Generate DML statements from Object Viewer

1. In Database Explorer, select the folder with the database object you want to generate a DML statement for and navigate to View > Object Viewer:

Object Viewer for a table

2. Right-click the required object and point to Generate Script As.

3. Hover over the DML statement you want to generate.

4. Select one of the following options:

  • To New SQL Window: This will open the statement in a new SQL window in dbForge Studio for SQL Server
  • To File: This will save the statement as a SQL file
  • To clipboard: This will copy the statement to the clipboard

DML for a table

Generate DML procedures

In dbForge Studio for SQL Server, you can generate DML procedures for the following database objects:

  • Table (INSERT/UPDATE STORED PROCEDURE, CRUD)
  • View (INSERT/UPDATE STORED PROCEDURE, CRUD)

Generate a CRUD statement from Database Explorer

1. Right-click a database object and point to Generate Script As.

2. Hover over the required CRUD statement.

3. Select one of the following options:

  • To New SQL Window: This will open the statement in a new SQL window in dbForge Studio for SQL Server
  • To File: This will save the statement as a SQL file
  • To clipboard: This will copy the statement to the clipboard

DML for a table

Generate a CRUD statement from Object Viewer

Similarly, you can generate a CRUD statement from Object Viewer.

For views, for example, you can generate the INSERT/UPDATE STORED PROCEDURE statement.

DML for a view

The ready DML procedure script will look like as follows.

DML script for a view

Generate EXECUTE statements

You can generate EXECUTE statements in dbForge Studio for SQL Server for:

  • Procedure (EXECUTE)
  • Table-valued function (EXECUTE)
  • Scalar-valued function (EXECUTE)
  • View (EXECUTE sp_refreshview)

Generate EXECUTE statements from Database Explorer

1. In Database Explorer, right-click the necessary object and hover over Generate Script As.

2. Point to EXECUTE.

3. Select one of the following options:

  • To New SQL Window: This will open the statement in a new SQL window in dbForge Studio for SQL Server
  • To File: This will save the statement as a SQL file
  • To clipboard: This will copy the statement to the clipboard

Execute for procedure

Generate EXECUTE statements from Object Viewer

1. Select the folder with a database object you want to generate the EXECUTE query for and navigate to View > Object Viewer:

Object Viewer for a procedure

2. Right-click a database object and hover over Generate Script As.

3. Point to EXECUTE.

4. Select one of the following options:

  • To New SQL Window: This will open the statement in a new SQL window in dbForge Studio for SQL Server
  • To File: This will save the statement as a SQL file
  • To clipboard: This will copy the statement to the clipboard

Generate Script for a procedure

If you want to generate a script for an entire database, refer to the Generate Scripts Wizard topic.

Customize the statement templates

dbForge Studio for SQL Server allows for configuring statement templates. To access the configuration options, follow these steps.

1. Navigate to Tools > Options:

Tools Options

2. Switch to Generate Scripts and select General:

General

3. Adjust the scripts options to your needs and click OK.

4. To configure the CRUD templates, switch to CRUD and select General, Select, Insert, Update, or Delete. Once you have completed the configuration, click OK.

General options

When you access the settings of the statements templates, you can see the following options:

Option name Option group Option description
Check for object existence Common Verifies whether an object exists in a target database.
Enclose identifiers within square brackets Common Inserts all identifiers into square brackets.
Generate comments in the script Common Puts comments to the script.
Script authorization Common Scripts authorization clauses.
Script collation Common Inserts collation infomation to the script.
Script default constraints Common Includes default constraints and schema defaults.
Script extended properties Common Adds extended properties of database objects to the script.
Script logins Common Places all logins available on a server.
Script security permissions Common Includes security permissions.
Script statistics Common Involves statistics.
Script USE DATABASE Common Generates the USE DATABASE query.
Security object name with its schema Common Prefixes object names with object schema.
Decrypt encrypted objects Tables/Views Decrypts bodies of encrypted objects for comparison and synchronization.
Enforce full column list Tables/Views Generates the INSERT statements with full-specified column list.
Script change tracking Tables/Views Adds CHANGE_TRACKING clauses and statements when the tool compares and synchronizes databases.
Script check constraints Tables/Views Includes the CHECK constraints to the script.
Script DATA_COMPRESSION for indexes, primary and unique constraints Tables/Views Involves the DATA_COMPRESSION claue for indexes, primary, and unique key constraints.
Script DML triggers Tables/Views Puts the CREATE statement of triggers on tables.
Script foreign keys Tables/Views Includes the CREATE query of foreign keys on tables.
Script full-text search Tables/Views Adds full-text stoplists, full-text catalogs, and full-text indexes.
Script indexes Tables/Views Puts the CREATE statement of indexes on tables.
Script primary keys Tables/Views Includes the CREATE query of primary keys on tables.
Script unique keys Tables/Views Involves the CREATE query of unique keys on tables.

CRUD options

The CRUD templates includes such options:

Option name Option description
General Allows you to configure whether to inlclude the SELECT/INSERT/UPDATE/DELETE statements.
Select Lets adjust the code template to your needs.
Insert Permits to tune the statement template.
Update Allows configuring the UPDATE query template.
Delete Empowers to alter the DELETE statement template.

Snippet placeholders

General placeholders

Placeholder Description
$table$ Specifies the name of the table for which you generate the CRUD procedures.
$schema$ Defines the name of the schema.
$col.Name$ States the column name of the table.
$col.AsParameter$ Determines the name of the procedure parameter converted from the column name of the table.
$col.Type$ Specifies the name of data type of the table column.
$parameters$ Defines the parameters of the $col.AsParameter$ and $col.Type$ separated by such symbols: “,” (comma) and “\n”(line feed).
$col.WhereName$ Verifies data type of the column. If it’s geography or geometry, the STEquals($col.AsParameter$) method call will be added.
$col.WhereAsParameter$ Specifies the name of the table column converted to the name of the procedure parameter or “1” if data type of the column is geography or geometry.
;format="id" States the formatting for any name. If a name is quoted, it will be quoted again. For example, ‘id’ will be ‘‘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$ States the columns list (specified in $col.Name$) separated by “,” (comma).
$where$ Determines the list of selection conditions combined with the AND operator. If “Return all data if input parameters are null” is selected, then ($col.WhereName$ = $col.WhereAsParameter$ OR $col.AsParameter$ IS NULL) the conditions will be vaialbale only for the PRIMARY KEY columns. Otherwise, “$col.WhereName$ = $col.WhereAsParameter$” will be available only for the PRIMARY KEY columns.

INSERT template

Placeholder Description
$procedure$ Defines the name of the procedure. For example, usp_$table$_Insert.
$columns$ Specifies the columns list of the table, except AUTO INCREMENT (IDENTITY) and GENERATED ALWAYS.
$values$ States inserted data $col.AsParameter$ separated by “,” (comma) for all the columns, except AUTO INCREMENT (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 AUTO INCREMENT (IDENTITY) and GENERATED ALWAYS columns, then the “$col.Name$ = SCOPE_IDENTITY()” condition will be available only for the AUTO INCREMENT (IDENTITY) columns. Otherwise, “$col.WhereName$ = $col.WhereAsParameter$” will be available for all the columns, except GENERATED ALWAYS.

Delete template

Placeholder Description
$procedure$ Specifes 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 AUTO INCREMENT (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.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.
Ready to start using dbForge Studio for SQL Server?
Download a free 30-day trial