You can create SQL scripts for your database objects and data using the Generate Script As command. You can then use these scripts to share, back up, migrate, or reuse objects and data across environments.
This section explains how to generate SQL scripts from Database Explorer, Object Viewer, and the results grid. It also describes how to customize script templates for standard CRUD operations.
The following table shows where you can access the Generate Script As command.
| Location | How to access | When to use |
|---|---|---|
| Database Explorer | Right-click an object in Database Explorer, select Generate Script As, then select the required option. | Use this method to generate DDL (Data Definition Language) scripts for objects such as tables, views, and procedures. You can also generate DML (Data Manipulation Language) templates, such as INSERT, UPDATE, or DELETE, with placeholder values based on the object structure. |
| Object Viewer | Right-click anywhere in the Object Viewer grid, select Generate Script As, then select the required option. | Use this method to generate DDL scripts and DML templates for the currently selected object. |
| Results grid | Right-click anywhere in the results grid, select Generate Script As, then select the required option. | Use this method to generate DML scripts such as INSERT, UPDATE, or WHERE IN. |
The following tables describe script generation options for database objects and data values in the results grid.
| Option | Description | Applies to |
|---|---|---|
CREATE |
Generates a script to create the selected object. | Tables, views, scalar-valued functions, table-valued functions, procedures, triggers, databases, users, roles, schemas, catalogs, synonyms, routes, assemblies, data types, XML schema collections |
DROP |
Generates a script to drop the selected object from the database. | Tables, views, scalar-valued functions, table-valued functions, procedures, triggers, databases, users, roles, schemas, catalogs, synonyms, routes, assemblies, data types, XML schema collections |
DROP and CREATE |
Generates a script that drops the selected object and then recreates it. | Tables, views, scalar-valued functions, table-valued functions, procedures, triggers, databases, users, roles, schemas, catalogs, synonyms, routes, assemblies, data types, XML schema collections |
CREATE OR ALTER |
Generates a CREATE OR ALTER statement for programmable objects such as functions and procedures. |
Scalar-valued functions, table-valued functions, procedures, DDL triggers |
ALTER |
Generates an ALTER statement to modify the object. |
Scalar-valued functions, table-valued functions, procedures, DDL triggers, routes |
SELECT |
Generates a SELECT statement. For tables and views, the statement includes all columns. For functions, it constructs a SELECT that calls the function. |
Tables, views, scalar-valued functions, table-valued functions |
INSERT |
Generates an INSERT statement with all columns and placeholder values. |
Tables, views |
UPDATE |
Generates an UPDATE statement with placeholder values and a WHERE clause based on key columns. |
Tables, views |
DELETE |
Generates a DELETE statement with a WHERE clause. |
Tables, views |
CRUD |
Generates the full set of SELECT, INSERT, UPDATE, and DELETE statements for the selected table. |
Tables |
INSERT/UPDATE STORED PROCEDURE |
Generates a stored procedure template that inserts or updates records in the selected table or view. | Tables, views |
EXECUTE |
Generates an EXEC statement with parameter placeholders for executing a scalar function or stored procedure. |
Scalar-valued functions, procedures |
EXEC sp_refreshview |
Generates an EXEC sp_refreshview statement to update the metadata of the selected view based on its referenced objects. |
Views |
| Option | Description |
|---|---|
INSERT |
Generates one or more INSERT statements for the selected values. |
BULK INSERT |
Generates a BULK INSERT statement to insert large datasets. |
INSERT #tmpTable |
Generates INSERT statements for a temporary table named #tmpTable. |
WHERE IN |
Generates a WHERE ... IN (...) clause from the selected values. |
WHERE AND OR |
Generates a WHERE clause with logical AND/OR conditions from the selected values. |
UPDATE |
Generates an UPDATE statement that uses the selected values and includes a sample WHERE clause. |
| Object type | Supported DDL statements |
|---|---|
| Aggregate functions | CREATE, DROP, CREATE OR ALTER, DROP and CREATE |
| Application roles | CREATE, DROP, DROP and CREATE |
| Assemblies | CREATE, DROP, DROP and CREATE |
| Asymmetric keys | CREATE, DROP, DROP and CREATE |
| Broker priorities | CREATE, DROP, DROP and CREATE, ALTER |
| Certificates | CREATE, DROP, DROP and CREATE |
| Column Encryption Keys | CREATE, DROP, DROP and CREATE |
| Column Master Keys | CREATE, DROP, DROP and CREATE |
| Contracts | CREATE, DROP, DROP and CREATE, ALTER |
| Constraints | CREATE, DROP and CREATE, DROP |
| Databases | CREATE, DROP and CREATE, DROP |
| Database roles | CREATE, DROP, DROP and CREATE |
| Database triggers | CREATE, DROP, CREATE OR ALTER, DROP and CREATE |
| Defaults | CREATE, DROP, DROP and CREATE |
| DML triggers | CREATE, DROP, ALTER, CREATE OR ALTER, DROP and CREATE |
| Event notifications | CREATE, DROP and CREATE, DROP |
| External Data Sources | CREATE, DROP, DROP and CREATE |
| Full-text catalogs | CREATE, DROP, DROP and CREATE |
| Full-text stoplists | CREATE, DROP, DROP and CREATE |
| Indexes | CREATE, DROP and CREATE, DROP |
| Message types | CREATE, DROP, DROP and CREATE, ALTER |
| Partition functions | CREATE, DROP and CREATE, DROP |
| Partition schemes | CREATE, DROP and CREATE, DROP |
| Procedures | CREATE, DROP, ALTER, CREATE OR ALTER, DROP and CREATE |
| Queues | CREATE, DROP, DROP and CREATE, ALTER |
| Remote service bindings | CREATE, DROP, DROP and CREATE, ALTER |
| Routes | CREATE, DROP, DROP and CREATE, ALTER |
| Rules | CREATE, DROP, DROP and CREATE |
| Scalar-valued functions | CREATE, DROP, ALTER, CREATE OR ALTER, DROP and CREATE |
| Schemas | CREATE, DROP, DROP and CREATE |
| Search property lists | CREATE, DROP and CREATE, DROP |
| Sequences | CREATE, DROP, DROP and CREATE |
| Services | CREATE, DROP, DROP and CREATE, ALTER |
| Statistics | CREATE, DROP and CREATE, DROP |
| Symmetric keys | CREATE, DROP, DROP and CREATE |
| Synonyms | CREATE, DROP, DROP and CREATE |
| Tables | CREATE, DROP and CREATE, DROP |
| Table-valued functions | CREATE, DROP, ALTER, CREATE OR ALTER, DROP and CREATE |
| Users | CREATE, DROP, DROP and CREATE |
| User-defined data types | CREATE, DROP, DROP and CREATE |
| User-defined table types | CREATE, DROP, DROP and CREATE |
| Views | CREATE, DROP, ALTER, CREATE OR ALTER, DROP and CREATE |
| XML schema collections | CREATE, DROP, DROP and CREATE |
| Object type | Supported DML statements |
|---|---|
| Aggregate functions | SELECT |
| Tables | SELECT, INSERT, UPDATE, DELETE |
| Table-valued functions | SELECT |
| Scalar-valued functions | SELECT |
| Views | SELECT, INSERT, UPDATE, DELETE |
| Object type | Supported DML procedures |
|---|---|
| Table | INSERT/UPDATE STORED PROCEDURE, CRUD |
| View | INSERT/UPDATE STORED PROCEDURE, CRUD |
| Object type | Supported statements |
|---|---|
| Procedures | EXECUTE |
| Scalar-valued functions | EXECUTE |
| Views | EXECUTE sp_refreshview |