Creating SQL Scripts

See Also

You can create and modify database objects in a database project using SQL script files. These files contain DDL (data definition language) SQL statements that define and modify database objects.

Note   In Microsoft® Visual InterDev™, you can use the Copy SQL Script command in the Data View window to create a SQL script that will create the selected database object when added to a database project and executed. For more information, see Populating a Database Project.

To create a new SQL script file and add it to a project in Visual InterDev or Visual J++™

  1. Expand the database project folder in the Project Explorer.

  2. Right-click the data connection and choose Add SQL Script.

    The Add Item dialog box is displayed.

    Tip   You can also right-click the project name and select Add Item to open the Add Item dialog box.

  3. Select SQL Script in the right pane. Enter a name for the SQL script in the Name box if you want. Click Open.

    The SQL Editor window is displayed and the SQL script is added to the Project Explorer under the data connection node.

  4. Enter SQL statements in the SQL Editor window that define the database objects you want to add to this database project. Follow the conventions of your database. For more information, see your database documentation.

You can also add specialized SQL scripts to your database project using the templates provided with the Visual Database Tools. You can select an SQL script file that creates a table, view, stored procedure, or trigger. You can use these supplied SQL script files as templates for creating your own SQL scripts

To create specialized script files based on templates in Visual InterDev or Visual J++

  1. Expand the database project folder in the Project Explorer.

  2. Right-click the data connection and choose Add SQL Script.

    The Add Item dialog box is displayed.

    Tip   You can also right-click the project name and select Add Item to open the Add Item dialog box.

  3. Select Table Script in the right pane. Enter a name for the SQL script in the Name box if you want. Click Open.

    The SQL Editor window is displayed and the SQL script is added to the Project Explorer under the data connection.

  4. Modify the existing SQL statements in the SQL Editor window to define the database object you want to add to this database project. Follow the conventions of your database. For more information, see your database documentation.

Once you've created the SQL script you want, you can execute it to create or modify database objects using the SQL statements in the SQL script file. You can execute the script file from Project Explorer using the Execute command or by double-clicking the script name in the SQL Editor. For more information, see Executing an SQL Script.

Note   An SQL script must be executed against a database. You can execute an SQL script against any database for which there's a data connection in your database project.

You can also modify database objects in the Data View window using the Visual Database Tools. For more information, see Working with Database Projects.

To create a new SQL script and add it to a project in Visual C++®

  1. From the File menu, choose New.

  2. Choose the Files tab, and then select ODBC Script File.

  3. Check Add to Project.

  4. Select the name of the database project you want to add the file to.

  5. In the File Name box, type a name for the script file, and then choose OK.

  6. Enter the SQL script, and then save it by choosing Save or Save As from the File menu.

After you have added an SQL script file to the project, you can add commands to the file. For example, you can create the following SQL script to display in the Output window the version number of a Microsoft® SQL Server™ database:

SELECT @@Version

You can run this script against any Microsoft SQL Server database.

You can also create an SQL script to execute a stored procedure. For example, in a Microsoft SQL Server database, you can create an SQL script that runs a built-in stored procedure, such as sp_help or sp_who.

To create an SQL script that executes a stored procedure

  1. Create a new SQL script file.

  2. Type Exec followed by the name of the stored procedure to execute, as in the following two commands:
    Exec sp_who
Exec sp_help
  3. Save the script.