Generating Scripts in SQL Server

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.

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. 

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

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

Here, I'm generating the script for an Employee database.

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

Right-click on the target database by selecting it. You will find the tasks option in that, click on tasks and it will open another submenu. In that submenu, you will find the option called 'Generate Scripts'.

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

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

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

Here change the property of "types of data to script" value to "Schema and Data".

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

Again click on "Next >".

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

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