Documenting and Scripting Databases

Creating and Maintaining Databases

Creating and Maintaining Databases

Documenting and Scripting Databases

With Microsoft® SQL Server™ 2000, you can document an existing database structure (schema) by generating one or more SQL scripts. An SQL script can be viewed in SQL Server Enterprise Manager, SQL Query Analyzer, or by using any text editor.

A schema generated as an SQL script can be used in many ways, including:

  • To maintain a backup script that will allow the user to re-create all users, groups, logins, and permissions.

  • To create or update database development code.

  • To create a test or development environment from an existing schema.

  • To train newly hired employees.

SQL scripts contain descriptions of the statements used to create a database and its objects. You can generate scripts from the objects in an existing database, and then add these objects to another database by running the scripts against that database. In effect, this re-creates the whole database structure and any individual database objects. The schema of the following objects can be generated and saved as a script.

Tables User-defined data types
Indexes Triggers
Views Users, groups, and roles
Stored procedures Logins
Defaults Rules
Table keys/declarative referential integrity (DRI) Object-level permissions
Full-text indexes  

The schema for the objects generated can be saved in a single SQL Script file, or in several files with each file containing the schema of just one object. You can also save the schema generated for a single object (or a group of objects) into one or more SQL script files. Examples of SQL script files that you can generate include:

  • An entire database saved into a single SQL script file.

  • Table-only schema for one, some, or all tables in a database saved into one or more SQL script files.

  • Table and index schema saved into one SQL script file, stored procedures saved into another SQL script file, and defaults and rules saved into yet another SQL script file.

To generate a script