An Overview of Database Projects

  • Article

A database project is the offline representation of a database schema. This representation contains the object definitions and deployment scripts that you would need to create a separate instance of that database or to update an existing instance. By creating a database project, you can put your database under version control.

You can perform the following tasks with database projects:

  • Create and deploy a database.

  • Put your database under version control.

  • Create a database project by importing the objects from an existing deployed database.

  • Compare your database project to a deployed database.

  • Import changes made to a deployed database so that the database project again matches the deployed database.

  • Add, modify, and delete database objects and scripts.

  • Create one or more data generation plans to populate your database with test data.

Importing Database Schemas

You can import a database schema from a database instance or from a script. When you import a database schema, it is validated. Statements that are not understood are put in the ScriptsIgnoredOnImport.sql file. If you import object definitions that reference objects that no longer exist, you must resolve those errors before you can build and deploy the database project. You can resolve the errors by deleting the object. For example, if you imported a stored procedure that referenced a table that no longer existed, you could simply remove that stored procedure.

The initial cleanup process might be time-consuming when you import a large database schema. However, after you have eliminated the errors, Team Edition for Database Professionals will make sure that new errors are not introduced. All changes are validated when you save the object definition that you have modified.

Database Project Structure

A database project consists of a .dbproj file and contains three default subfolders that are named Data Generation Plans, Schema Objects, and Scripts. Each subfolder is described in more detail later in this topic. Solution Explorer provides a file-based view of your database project. Each item that appears in Solution Explorer corresponds to a file or a folder on disk. In contrast, Schema View provides an object-based view so that you can identify objects in your database whether they exist as separate files or not.

In Solution Explorer, you can perform actions on the project as a whole or on individual files. You can perform the following actions on a database project in Solution Explorer:

  • Import Database Schema
    You can import the schema information from an existing database that you specify.
  • Import Script
    You can import the schema information for one or more objects from a script that contains object definitions.
  • Build
    You can generate a build script that creates a database with the same schema or synchronizes an existing database with that schema. The properties of the database project control the deployment as well as the settings on the target database.
  • Rebuild
    You can use this option to build the project, even if no changes have been made since the last time that you built it.
  • Deploy
    You can deploy your database project to a new or an existing database, based on your selected configuration. This action also builds the database project before deploying it if it is not up to date.
  • Clean
    You can delete any existing build scripts that were generated as a result of a build, rebuild, or deploy operation.
  • Add
    You can add folders and project item files to your database project. For example, you can add project items that contain database object definitions.
  • Set as Startup Project
    You can specify that the database project should be marked as the startup project for the solution. If you have other projects in your solution, one of them would probably be a better choice as a startup project because database projects contain no user interface elements.
  • Add Solution to Source Control
    You can put the solution that contains your database project under version control.
  • Cut
    You can copy the project to the Clipboard. This action also marks the project so that it will be removed from its current location in the solution when you paste it somewhere else.
  • Remove
    You can remove the database project from the current solution.
  • Rename
    You can rename the database project. If the project is under version control, it will be checked out to complete this action.
  • Unload Project
    You can unload the database project. You might want to unload a project if you open a large solution that contains multiple projects and you wanted to load only the projects upon which you are actively working. After you unload a database project, you can use the Reload Project command to reload it.
  • Show All Files
    You can toggle this setting to show or hide files that you have excluded from the database project.
  • Properties
    You can view and customize the project properties, which control aspects of build and deployment for the database project.
  • Refresh
    You can refresh the database objects shown in Solution Explorer. This will reinterpret the contents of the files that contain the object definitions.

Data Generation Plans

By default, this subfolder contains any plans that you have created to generate representative test data for a deployed database.

You can perform the following actions on the Data Generation Plans subfolder:

  • Add New Item
    You can create a project item, such as a data generation plan, and add it to the project.
  • Add Existing Item
    You can add an item that already exists to your database project. A copy of that item is made in your project folder or a subfolder of the project folder.
  • Copy
    You can copy all data generation plans for the project to the Clipboard.

Schema Objects

By default, this subfolder contains all files that contain the definitions for objects in your database project, grouped by object type.

You can perform the following actions on the Schema Objects subfolder:

  • Add New Item
    You can create a project item, such as a table definition, and add it to the project.
  • Add Existing Item
    You can add an item that already exists to your database project. A copy of that item is made in your project folder or a subfolder of the project folder.
  • Copy
    You can copy all schema objects in the project to the Clipboard.

Database schema objects are defined in a collection of .sql files that are stored in the project folder. Most objects are defined in separate files. Exceptions include columns in a table (which are specified in the file that contains the table definition) and parameters to a stored procedure or function (which are specified in the file that contains the stored procedure or function).

The Build Action property for a .sql file indicates whether it is parsed to see if it contains the definition of a database object. By default, files that contain database object definitions are set to Build, and other .sql scripts and miscellaneous files are set to Not in Build. The Build Action property also determines whether the file is included in the build script.

The name of each object determines its file name, and the type of object determines its default file extension, as follows:

Database Object Type Name

Function

ObjectName.function.sql

Index

ObjectName.index.sql

Stored Procedure

ObjectName.proc.sql

Table

ObjectName.table.sql

Trigger

ObjectName.trigger.sql

View

ObjectName.view.sql

Primary Key Constraint

ObjectName.pkey.sql

Foreign Key Constraint

ObjectName.fkey.sql

Unique Key Constraint

ObjectName.ukey.sql

Check Constraint

ObjectName.chkconst.sql

Default Constraint

ObjectName.defconst.sql

Statistic

ObjectName.statistic.sql

If you define an object within the .sql file whose object type does not match the object type implied by the file name, an error appears in the Error List window. For example, if you added a file named MyTable.table.sql to the database project but defined a view within that file, an error message appears in the Error List window.

You can perform the following actions on files in the Schema Objects subfolder:

  • Open
    Opens the file in the appropriate editor, depending on the file type. For example, the Transact-SQL (T-SQL) editor opens for .sql files.
  • Open With
    Opens the file that contains the object definition in another editor that you specify.
  • View Code
    Opens the file in the T-SQL editor.
  • Exclude from Project
    Marks the file as excluded from the project. Excluded files are not part of the build script that is generated when you next build or deploy the database project. You might exclude a file if it is still in progress and you want to deploy the rest of the database.
  • Cut
    Copies the selected file or files to the Clipboard and deletes them from the database project.
  • View Object in Schema View
    Opens Schema View if it is not already displayed and highlights the database object whose definition was contained in the selected file that you clicked. This option is only available for .sql files that are contain valid object definitions.
  • Copy
    Copies the selected file or files to the Clipboard.
  • Rename
    Renames the file. This does not perform rename refactoring on the contents of the file, but is instead a simple file rename.
  • Delete
    Deletes the selected file or files from the database project and removes them from disk. You are then asked to confirm the deletion because it is permanent.
  • Properties
    Displays the properties window for the file or files that you clicked.

Scripts

By default, this subfolder contains management scripts, pre-deployment scripts, and post-deployment scripts for your database. The Build Action property is set to Not in Build for management scripts and to either PreDeploy or PostDeploy for deployment scripts. You can have only one pre-deployment and one post-deployment script in a database project. Pre-deployment and post-deployment scripts often include additional scripts. The additional scripts might also be part of your database project and will have the Build Action property set to Not in Build.

You can perform the following actions on the Scripts subfolder:

  • Add New Item
    You can create a project item, such as a management script, and add it to the project.
  • Add Existing Item
    You can add an item that already exists to your database project. A copy of that item is made in your project folder or a subfolder of the project folder.
  • Copy
    You can copy all scripts in the project to the Clipboard.

Schema View

Schema View shows you an alternative view of your database project, focusing on the database objects that it contains. Neither data generation plans nor scripts have any corresponding entry in Schema View, because neither type of item contains definitions of database objects.

You can perform the following actions on the project node in Schema View.

  • Import Database Schema
    You can import the schema information from an existing database that you specify.
  • Import Script
    You can import the schema information for one or more objects from a script that contains object definitions.
  • Compare Schema
    You can compare the schema information for the database project to that of a deployed database.
  • Add
    You can add database objects to your database project, including tables, views, stored procedures, inline functions, table functions, scalar functions, user-defined data types, file groups, database application roles, database roles, and database users.
  • Properties
    You can view the database properties.

Note

You cannot add symmetric keys, asymmetric keys, and certificates as objects to the database project. You must create them in the pre-deployment script. If you import the schema from a database that contains these objects, the pre-deployment script will contain placeholder comments with the names of the keys and certificates. You must modify the pre-deployment script to create those objects.

  • Refresh
    You can refresh the database objects shown in Schema View. This will reinterpret the contents of the files that contain the object definitions.
  • Solution Explorer
    You can toggle focus from Solution Explorer to Schema View.
  • Filter Schema View
    You can toggle the display of objects by Object Type or by Schema. You must select a project node or an item within a database project to toggle the grouping.

Schema Objects

Schema View contains a node for each database project in Solution Explorer. Each database project node contains all the definitions for objects in your database project, grouped by object type, or by schema.

You can perform the following actions on the database objects that appear in Schema View:

  • Import Script
    You can import the schema information for one or more objects from a script that contains object definitions.
  • Open
    You can open the object definition in the T-SQL editor. Changes that you make in the file might cause updates to Schema View. For example, if you add columns to a table definition, those columns will appear in Schema View.
  • Open With
    You can open the file that contains the object definition in another editor that you specify.
  • Add
    You can add database objects to your database project, including tables, views, stored procedures, inline functions, table functions, scalar functions, user-defined data types, file groups, database application roles, database roles, and database users. In addition, you can add folders within the database project to organize objects.
  • Rename (on the Refactor sub-menu)
    You can rename the database object. If the object is under version control, the file that contains the object definition will be checked out to complete this action. This action invokes the refactoring operation and can update all references to the renamed object within the database project and any associated database unit test projects.
  • View File in Solution Explorer
    You can use this option to find the file in Solution Explorer that contains the definition for the database object that you clicked.
  • Properties
    You can view the object properties. Nearly all the properties that are available for objects in Schema View are read-only. To change the properties, you must edit the object definition.

Modifying the Default Project Structure

The structure described in this topic is the default project structure in Solution Explorer, but you can rearrange the contents of the project. You can create additional folders, rename existing folders, and move files within the project by dragging them or by performing a cut-and-paste operation. Database object definitions are identified by their Build Action property, which is set to Build, not by their location within the project. Deployment scripts are also identified by their Build Action property, which is set to either PreDeploy or PostDeploy. The Build Action property for other files in the project is set to Not in Build.

You can only toggle the display of Schema View to show the contents of a project by Object Type or by Schema.

See Also

Concepts

An Overview of Database Creation and Deployment in a Team Environment
Working with Database Objects
Terminology Overview of Team Edition for Database Professionals

Other Resources

Building and Deploying Version-controlled Databases
Working with Database Scripts
Working with Database Projects in a Team Environment
Walkthroughs (Creating and Updating Version-controlled Databases)