How to generate the DDL and DML statements for database objects

Generating predefined DDL, DML, CRUD, and EXECUTE statements based on existing database objects is a common task for database specialists. It is essential in such scenarios as database migration, source code recovery, reverse engineering, replication, backup, auditing, performance tuning, and more.

Automatically generating these statements not only saves time but also reduces errors, ensures standardization, and simplifies all database operations. dbForge Studio for SQL Server, a versatile integrated development environment (IDE) for SQL Server databases, offers automated statement generation of such statements and provides additional advantages for users:

A user-friendly and intuitive interface
One-click script generation for various database objects
Ability to generate scripts directly from Database Explorer and Object Viewer
Options to save scripts to files, clipboard, or new SQL windows
On-the-fly SQL code formatting for the generated script
Snippet placeholders and templates

This way, dbForge Studio for SQL Server streamlines the generation of predefined statements and procedures and ensures accuracy. Now, let's explore how this IDE can create DDL, DML, CRUD, and EXECUTE statements effortlessly.

Generate statements from Database Explorer and Object Viewer

In dbForge Studio for SQL Server, you have two options for generating DDM and DDL statements. These can be generated either from the Database Explorer area or from the Object Viewer window.

If you want to generate the statement from the Database Explorer:

1. Right-click the required database object and select Generate Script As.
2. Hover over the DDL statement you want to generate.
3. Choose the desired option:

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

To generate a DDL statement from the Object Viewer, do the following:

1. Open the Object Viewer feature by selecting it in the View menu (or simply clicking F7).
2. To access the database object you need, simply choose 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 desired database object, right-click on it and choose Generate Script As. This will present you with the same options available as found in the Database Explorer pane.

In the following examples, we will demonstrate how to generate different DDM and DDL statements using dbForge Studio for SQL Server. You can access this functionality either from the Database Explorer or the Object Viewer window. Both of these options are equally capable, and your choice should be based solely on your personal preferences.

Generate the DDL statements

In dbForge Studio for SQL Server, you can generate the DDL statements for the following database 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)

Below, we are generating the CREATE OR ALTER statement for the scalar-valued function:

As we chose the To a New SQL Window option, the Studio demonstrates the created script:

Generate the DML statements

The following DML statements are supported:

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

Let us generate the INSERT statement for the Employee table:

The result is below:

Generate the DML procedures

The DML procedures can be generated for such database objects:

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

We are going to use dbForge Studio for SQL Server to generate the CRUD DML procedure for the Employee table:

The result is:

For views, we can generate the INSERT/UPDATE STORED PROCEDURE statement. Let us create it for the vEmployee view:

Generate the INSERT or UPDATE procedure for a view

And the ready DML procedure script is presented below:

View the INSERT/UPDATE procedure script for a view

Generate the EXECUTE statements

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

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

Assume we want to generate the EXECUTE sp_refreshview statement for the vIndividualCustomer view:

Generate the EXECUTE sp_refreshview statement for a view

Statement templates in dbForge Studio for SQL Server

dbForge Studio for SQL Server provides a wide range of features designed to enhance SQL coding processes and ensure the delivery of high-quality results. One noteworthy feature is the statement templates, which enable developers to use predefined, thoroughly tested, well-formatted, and polished templates for various operations, streamlining SQL code creation.

To access special options for configuring the statement templates in dbForge Studio for SQL Server, navigate to Tools > Options.

Proceed to the Generate Scripts section.

Here, you can access both General and CRUD configuration options, which we'll thoroughly review.

General options

The General settings of the statement templates provide 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 information 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 a 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 clause 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 following options are available for configuring the CRUD statement templates.

Option name	Option description
General	Lets you configure whether to include 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.

Customize the settings to suit your requirements. Navigate to either the General or CRUD section, and choose the appropriate template for the task you wish to configure. Once you've completed the setup, simply click OK.

Snippet placeholders

The placeholders below represent specific parameters used in statement templates. This allows users to fine-tune the templates more accurately to meet their specific requirements.

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 the table column data type.
$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’’.

Next, we'll present the specific snippet placeholders for the SELECT, INSERT, UPDATE, and DELETE templates.

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 available 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.

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.

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.

Conclusion

The automation of DDM and DDL statement generation is made effortless by utilizing the robust features of dbForge Studio for SQL Server. This software not only streamlines the database management and development processes significantly but also enhances readability. Thanks to its user-friendly interface and comprehensive scripting options, database professionals can save valuable time and minimize the risk of human errors.

Furthermore, the benefits extend beyond time-saving and error reduction. They foster better collaboration between development and database administration teams, enabling them to work together seamlessly and iteratively. This, in turn, promotes greater agility and adaptability.