Articles → SQL SERVER → Generate script for database objects using SSMS

Generate script for database objects using SSMS

In this article, we will generate script(s) for database objects like tables, views, stored procedures etc. using SQL Server Management Studio.

Database schema

For this article, I am using a database with some dummy tables. See the screen shot below

Click to Enlarge

Steps

For generating the script

1. Right click on the database, and go to ‘Tasks→ Generate Scripts…’

Click to Enlarge

2. Following window appears on click of ‘Generate Scripts…’

Click to Enlarge

Click on next button.

3. Following window will appear

Click to Enlarge

In the above window, you can select the first radio button i.e. ‘Script entire database and all database objects’ to generate the script for all database objects.

Or you can select the second radio button i.e. ‘Select specific database objects’, if you want to generate script(s) for the particular object(s).

Click on Next.

4. Following window will appear

Click to Enlarge

In the above window, you can select either of these options

   1. Save the generated script as file

   2. Copy it on clipboard

   3. Show it in new query window.

   4. Now click on Next and following window will appear

Click to Enlarge

5. Click Next and scripts will be generated for the database objects as shown in figure below

Click to Enlarge

6. Click on Finish to complete the process. A script file will be created on specified path

Click to Enlarge

There is a ‘Advanced’ button that will open ‘Advanced Scripting Options’ window as shown in figure below

Click to Enlarge

There are many options in this window, but we will only talk about few options

   1. Script Drop and Create – There are three options

      a. Create – When you want to generate ‘create’ script only for database objects

      b. Drop – When you want to generate ‘drop’ script only for database objects.

      c. Drop and Create – When you want to generate ‘drop and create’ script for database objects.

Click to Enlarge

   2. Types of data to script – There are three options

      a. Data only – When you only want to generate scripts for data only. This option is only valid for tables.

      b. Schema and Data – When you want to generate scripts for schema of data objects and data of tables.

      c. Schema only – When you want to generate scripts for schema only.

Click to Enlarge

3. Script Indexes – It means while generating the script for tables, you also want to create a script for indexes as well then you can select this option to true.

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Friday, April 20, 2018

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250