Generating SQL Scripts

SQL Scripts are descriptions of the statements that are used to create database objects. With SQL Enterprise Manager, you can generate scripts from objects in an existing database. You can then add these objects to a database by running the scripts against that database. In effect, this copies and re-creates database objects. Another reason to generate scripts is to create a record of how a database was created.

You can generate a script for an entire database or for selected objects of that database.

    To generate a SQL script
  1. In the Server Manager window, select a server, open its tree (click the "" box), open its Databases folder, and select a database.
  2. From the Object menu, choose Generate SQL Scripts.

    The Generate SQL Scripts dialog box appears.

  3. To generate a script for the entire database, under Scripting Objects, choose All Objects.

    All objects in the database are added to the right-hand box at the bottom of the Generate SQL Scripts dialog box. Optionally, to exclude selected objects, select the objects, and choose Remove.

  4. To generate a script for all objects of a particular type, under Scripting Objects, select the option for that object type.

    For example, to generate a script for all tables in the database, choose the All Tables option.

    The objects are added to the right-hand box at the bottom of the Generate SQL Scripts dialog box. Optionally, to exclude selected objects, select the objects and choose Remove.

  5. To generate a script for a particular object, select the object from the list in the left-hand box at the bottom of the Generate SQL Scripts dialog box, and then choose Add.

    The object moves to the right-hand box. If you make a mistake, select the object from the right-hand box, and then choose Remove.

  6. Under Scripting Options, select the options for the script.
    Object Creation
    When selected, adds object-creation statements to the script. For example, the CREATE TABLE statement.
    Object Drop
    When selected, adds statements that drop objects before creating them. For example, the DROP TABLE statement.
    Table Indexes
    When selected, adds the statements used to create the indexes for a table. Provides the CREATE INDEX statement.
    Table Triggers
    When selected, adds statements used to create triggers for a table. Provides the CREATE TRIGGER statement.
    Table DRI
    When selected, adds statements to create declarative referential integrity statements for a table. Provides the CREATE TABLE statement with the constraint information.
  7. To include security information in the script, select the options under Security.

    Select the options for the user information to be generated with the script. You can generate statements that add all users and groups to the database, that add all logins to SQL Server of the users associated with that database, and/or that add the permissions for the database.

  8. To preview or modify the script before generating it, choose Preview.

    The Object Scripting Preview dialog box appears.

    To modify the script, edit the text in the Object Scripting Preview box, choose the Save As button, and complete the Save As dialog box that appears.

  9. To generate the script, choose Script.

    The Save As dialog box appears.

  10. Select a script file option.
  11. Give the script a name ending in the .SQL filename extension, specify the directory, and then choose OK.

Note When a script is run, it creates the database object(s), but it does not include data to a table that it creates. Data can be added, using the INSERT statement or can be copied in from the original table by using bcp or SQL Transfer Manager. When running a script in a non-empty database, the script might not be able to drop and/or create some items correctly (for example, user-defined datatypes) if those items are being used by other database objects.