Scripts

A script is usually defined as a collection of Transact-SQL statements (in one or more batches) in the form of an external file. Client tools, such as Management Studio and SQLCMD, usually have support for managing script files.

Scripts are usually stored in plain text files with a .sql extension. This makes them manageable from any text editor as well as from many sophisticated tools, such as the Microsoft application for code control, Visual SourceSafe.

Management Studio has the basic script management features as any text editor, such as File | Open, Save, and some advanced features to generate scripts of database objects or to manage scripts as a part of project and solution files. SQLCMD is a command line utility that allows the user to specify script files with code to be executed against the server.

Database Scripting

One of the most exciting features in Management Studio is the ability to perform reverse engineering on the database without the need for external tools. The result of this process is a script that contains DDL statements, which can be used to re-create the database objects included in the script. This script can be used to

• Explore user and system database objects

• Back up source code

• Establish a source control process

• Transfer the complete database (or just some objects) to another server (and/or another database)

The process of database scripting is very simple:

1. Open a context-sensitive menu of a database in Management Studio.

2. Select Tasks | Generate Scripts and the program will open the Generate SQL Server Scripts Wizard (see Figure 5-2).

   
   Figure 5-2: Generate SQL Server Scripts Wizard

3. On the next screen the wizard will prompt you to select the database that you want to script.

4. If you select Script All Objects in the Selected Database checkbox, you will be able to click Finish and complete the process.

5. If you leave the checkbox unmarked, the wizard will prompt you to choose script options (see Figure 5-3).

   
   Figure 5-3: Script options

6. Carefully browse the values in this dialog box. It is likely that you want to change some defaults. For example, you probably want to include indexes and object-level permissions.

7. The wizard then prompts you to choose the major object types that you want to script.

8. If you choose, for example, tables, the stored procedures program will prompt you to select which tables and stored procedures you want to script.

9. In the Output Option window you have to choose whether scripts will be sent to a file (using Unicode or the ANSI character set), the Clipboard, or a Query window.

   Tip 

   If you want to be able to open a script file from regular editors (tbat do not support Unicode) sucb as Notepad, you sbould select ANSI as your file format.

10. You can now click Finish to start the process (or Next to see a summary of specified options).

11. The wizard will display progress in the Generate Script Progress window (see Figure 5-4). After it's done, you can click the Filter and Report buttons to manipulate the status of individual actions.

Figure 5-4: Generate Script Progress window

Tip 

Use database scripting to explore the sample database associated witb tbis book and the sample and system databases published witb SQL Server. Exploration of otber styles and methods in coding will help you to gain knowledge and build experience.