Working with Database Objects

Article
06/26/2007

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.

Note

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.

Note

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.

Share via