• Article

Documenting and Scripting Databases

With SQL Server, you can document an existing database structure, called a schema, by generating one or more SQL scripts. An SQL script can be viewed in the SQL Server Management Studio Query Editor, or by using any text editor.

A schema generated as an SQL script can be used to do the following:

  • Maintain a backup script that lets the user re-create all users, groups, logins, and permissions.

  • Create or update database development code.

  • Create a test or development environment from an existing schema.

  • 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 complete database structure and any individual database objects.

The schema for generated objects 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 the following:

  • A complete 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