Working with Database Objects

Database objects define the structure of the contents of your database. They are contained within a database project that can also include data generation plans and scripts. In Solution Explorer, database objects are defined in files and grouped by type under the Schema Objects subfolder within the database project. When you work with database objects, you might find it more intuitive to use the view of the database objects called Schema View. In Schema View, you see the database objects by category. A file in your database project is parsed for a database object definition if it is marked with Build Type of Build. Files that are contained in your database project with a different Build Type are not treated as though they contain database objects and do not appear in Schema View.

Solution Explorer

In Solution Explorer, you operate on the files that are contained within your project. You can perform the following action on subfolders within the Schema Objects subfolder in Solution Explorer:

  • Add - adds an item of a type that you specify to the appropriate location in the database project hierarchy.

You can perform the following actions on all files within the subfolders in Solution Explorer:

  • Open - opens the file in the Transact-SQL (T-SQL) editor.

  • Cut - copies the file to the Clipboard and removes it from the database project.

  • Copy - copies the file to the Clipboard.

  • Delete - permanently removes the file from the database project.

  • Rename - renames the selected file.

  • View Object in Schema View - selects the object in Schema View that is contained in the selected file. Schema View appears if it was not already visible.

  • Properties - displays the Properties window, which lists the properties of the file.

You can perform additional actions on some object types, depending upon the type of object.

Schema View

In Schema View, you operate on the database objects. You can perform the following actions on subfolders within the Schema Objects subfolder in Schema View:

  • Add - adds an object of a type that you specify to the appropriate location in the database project hierarchy.

You can perform the following actions on all database objects within the subfolders:

  • Open - opens the definition of that object in the T-SQL editor.

  • Cut - copies the object to the Clipboard and removes it from the database project.

  • Copy - copies the object to the Clipboard.

  • Delete - removes the item from the database project.

  • Refactor: Rename - renames the selected item and its dependencies based on the principles of database refactoring.

  • Properties - displays the Properties window, which lists the properties of the object.

Object Name Resolution

In general, object names should be resolved using the same rules that SQL Server uses. Some sysobjects might need to be fully qualified to resolve properly. If you receive errors or notice unexpected behavior in cases that involve object names that are not fully qualified, you should fully qualify the object name to try to resolve the issue.

Referencing the Database Name in Object Definitions

You can use the $(databasename) token in object definition scripts in the database projects. The following restrictions apply:

  • You must enclose the token in square brackets as in the following example:

    [$(databasename)]

  • You cannot use the token to specify a three-part name as in the following example:

    [$(databasename)].[dbo].[Table1]

The following example shows how to use the $(database) name token in a stored procedure:

CREATE PROCEDURE [dbo].[MyProcedure]
@param1 INT = 0,
@param2 INT
AS
ALTER DATABASE [$(databasename)]
MODIFY FILEGROUP [FileGroup1] DEFAULT
RETURN 0

Object-level SET Options

In addition to the SET options that you can set for your database project, you can also specify values for two options on individual database objects. These options are ANSI nulls and Quoted identifier. When you create a database object, the SET options for that object match the database project's SET options by default. You can set these two options to one of three values: Project default, On, or Off. When you build the database project, only those object-level SET options that are set to a different value than the project default are scripted in the build script.

NoteNote

When you use the Import Script or Import Database Schema commands, SET option values are not imported for individual database objects. You must set them in the Properties window. For more information, see How to: Specify Object-level SET Options.

Database Object Validation

Validation occurs when you save changes to your new or modified database object. Any errors in the object definition appear in the Error List window. If you double-click the error message, the object definition opens so that you can correct the error. Database objects that contain errors show a red exclamation point on their icons in Schema View.

NoteNote

Database objects that are not yet supported show an inverted yellow triangle in their icons.

For more information about how to troubleshoot issues with database objects, see Troubleshooting Database Project and Version Control Issues.

Types of Schema Objects in Schema View

Your database project contains the definitions for all objects in the database schema. The specific list of object types and the actions that you can perform on those object types depend on the version of Microsoft SQL Server that you are running.

Object Type SQL Server 2000 SQL Server 2005

Tables

Supported

Supported

Views 

Supported

Supported

Synonyms

 

Supported

Stored Procedures

Supported

Supported

Functions

Supported

Supported

Database Triggers

 

Supported

Assemblies

 

Supported

Types: User-defined Data Types

Supported

Supported

Types: User-defined Types (CLR)

 

Supported

Types: XML Schema Collections

 

Supported

Service Broker

 

Supported

Service Broker: Message Types

 

Supported

Service Broker: Contracts

 

Supported

Service Broker: Queues

 

Supported

Service Broker: Services

 

Supported

Service Broker: Routes

 

Supported

Service Broker: Event Notifications

 

Supported

Service Broker: Remote Service Binding

 

Supported

Storage: Full Text Catalogs

Supported

Supported

Storage: Partition Schemes

 

Supported

Storage: Partition Functions

 

Supported

Storage: File Groups

Supported

Supported

Security

Supported

Supported

Security: Users

Supported

Supported

Security: Roles

Supported

Supported

Security: Roles: Database Roles

Supported

Supported

Security: Roles: Application Roles

Supported

Supported

Security: Schemas

Supported

Supported

Orphaned Objects

The Orphaned Objects folder in Schema View is a temporary location for objects that are associated with other objects that are not currently defined within the project. For example, if you create an index on a table but the table does not exist or is not currently valid, that index appears in the Orphaned Objects folder. When the parent object is created (or becomes valid), the child object appears in the correct location within the project.

In This Section

How to: Import Database Objects from Script

Describes how to import a database definition from existing T-SQL scripts that you might already have.

How to: View Database Objects

Describes how to display Schema View, which shows the contents of the database project organized by object instead of by file.

How to: Create a Database Object

Describes how to create database objects, such as tables, views, and stored procedures.

How to: Modify Database Objects

Describes how to modify the definitions of database objects, such as tables, views, and stored procedures.

How to: Delete Database Objects

Describes how to delete database objects from the database project.

How to: Add a User to a Role

Describes how you can associate a database user with a role in the post-deployment script.

How to: Define Full-text Catalogs

Describes how to define full-text catalogs on a particular filegroup.

How to: Specify Object-level SET Options

Describes how to set the ANSI nulls and Quoted identifiers options for individual database objects to override the database project settings.

An Overview of Templates for Database Objects

Provides background information about the templates that you can use to create database objects and database scripts.

See Also

Show: