Scripts and Transactions in Transact-SQL
(Page 1 of 5 )
Last week, we discussed batches in relation to Transact-SQL statements. This week, we touch on scripting and begin our examination of transactions. This article, the second of a three-part series, is excerpted from chapter five of the book
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281).
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:
- Open a context-sensitive menu of a database in Management Studio.
- Select Tasks | Generate Scripts and the program will open the Generate SQL Server Scripts Wizard (see Figure 5-2).
- On the next screen the wizard will prompt you to select the database that you want to script.
- If you select Script All Objects in the Selected Database checkbox, you will be able to click Finish and complete the process.
- If you leave the checkbox unmarked, the wizard will prompt you to choose script options (see Figure 5-3).
Figure 5-2. Generate SQL Server Scripts Wizard
- 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.
- The wizard then prompts you to choose the major object types that you want to script.
Figure 5-3. Script options
- If you choose, for example, tables, the stored procedures program will prompt you to select which tables and stored procedures you want to script.
- 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 (that do not support Unicode) such as Notepad, you should select ANSI as your file format.
- You can now click Finish to start the process (or Next to see a summary of specified options).
- 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.
TIP
Use database scripting to explore the sample database associated with this book and the sample and system databases published with SQL Server. Exploration of other styles and methods in coding will help you to gain knowledge and build experience.
Figure 5-4. Generate Script Progress window
Next: Transactions >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Buy this book now.
|
|
/ 4 
