Documenting and Scripting Databases

With Microsoft® SQL Server,™ 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 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 that were 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

Documenting and Scripting Databases

See Also