Network Administration: SQL Management Studio Scripts

Although you can create database objects by using the graphical features and wizards SQL Server provides it’s actually better practice to write SQL scripts that contain the CREATE commands necessary to create the database as well as its tables and other objects.

That’s because during the development and operation of any database application, there’s often a need to delete the database and re-create it from scratch. By scripting these actions, you can delete the database and re-create it simply by running a script.

Fortunately, SQL Management Studio can generate scripts from existing databases and tables. Thus, you can use the visual design features of SQL Management Studio to initially create your databases. Then, you can generate scripts that will let you easily delete and re-recreate the database.

To create a script for a database or table, just right-click the database or table, and then choose one of the Script As commands. For example, the following figure shows the script that results when the Movies table is right-clicked and Script Table As→CREATE To→New Query Window is chosen.

As you can see, this command generated a CREATE TABLE statement along with other advanced SQL statements to create the Movies table automatically.

image0.jpg

After you’ve created the script, you can save it to a text file by clicking the Save button. Then, you can run the script at any time by following these steps:

  1. Choose File→Open→File.

  2. Select the file you saved the script to.

  3. Click Open.

  4. Click the Execute button to run the script.

The only limitation of this technique is that although you can generate scripts to define your databases and tables, you can’t automatically generate scripts to insert data into your tables. If you want scripts that insert data, you have to manually create the INSERT statements to insert the data.