Generating Scripts in SQL Server

Hello friends. This article explains how to generate the scripts in SQL Server.

SQL Server Management Studio provides two mechanisms for generating Transact-SQL scripts. You can create scripts for multiple objects using the Generate and Publish Scripts Wizard. You can also generate a script for individual objects or multiple objects by using the "Script as" menu in Object Explorer.

Open the SQL Server Management Studio.

SQL SERVER management Studio

It will open one window named "Connect to server".

We have two authentication modes in SQL Server.

  1. Windows Authentication Mode
  2. SQL Server Authentication Mode

If you are connecting with Windows Authentication Mode then specify the server name and then click on connect in the connect to server window.

connect database engine

If you are connecting using SQL Server Authentication Mode then specify the server name, login id and Password and then click on connect in the connect to server window.

connect to database

After connecting to the server you will find the Object Explorer in the left side. In that all your databases will appear in the databases option. Explore the databases option and select the database or table you want to generate the script for.

Here in my system I'm generating the script for an Employee database.

So here the screens are for the Employee Database. It may be for others in your PC.

Okay anyway select your database and right-click on that database. You will find the tasks option in that, click on tasks and it will open another sub menu. In that sub menu you will find the option Generate Scripts.

generate script

Click on that option. It will open another window named "Generate and Publish Scripts". Click on "Next >" in that window.

generate ans publish script

Now it will ask you to select the database object to generate the script for. In this we have two options, one is "Script Entire database and all database objects" and the second one is "Select specific database objects".

If you want to generate the script for the entire database then select the first option.

If you want to generate the script for specific tables then select the second option. Then click on "Next >".

Script entire database

Provide the file name, whatever you want and next click on "Advanced".

advance script option

In this change the property of "types of data to script" to "Schema and Data".

advance script option in sql

types of data to script

Then click on "OK" and then choose the path where you want to save the script to. Then click on "Next >".

review your select option

Again click on "Next >".

finish

Click on "Finish" and now go to your folder where your script is saved and open it.

your script

You can use this script in any other machine that has SQL Server installed and you can create the same database and tables there also.

To do this open the script file in SQL Server Management Studio and execute the file. That's it, your database is ready.

And it is important to undersatnd that in a real scenario for projects you need to generate the scripts for the database that you are using for that project and should keep those scripts in that project folder.


Similar Articles