Scenario: Create and Modify Database and Server Objects

Database objects define the structure of the contents of your database. These objects are contained within a database project, which can also include data generation plans and scripts. Server objects define objects that exist on the database server but not in a database, such as logins, certificates, or custom error messages. Server objects are contained within server projects.

In Solution Explorer, definitions for database and server objects are contained in files and grouped by type within the database or server project. When you work with database and server objects, you might find it more intuitive to use Schema View, in which objects are grouped by type. A file in your database project is parsed for a database object definition if the file is marked with a Build Type of Build. Files that are contained in your database project but that have a different Build Type are not treated as if they contain database objects and do not appear in Schema View.

Common Tasks

In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.

Task	Supporting Content
Get hands-on practice: You can follow introductory walkthroughs to become familiar with how to create a database project and its objects and how to modify those objects.	Walkthrough: Create and Deploy a New Version-Controlled Database Walkthrough: Modify Database Objects
Learn more about database and server objects: Later in this topic, you can learn more about database and server objects, including how names are resolved, how to refer to database names in object definitions, and how objects are validated.	Object Definitions Objects in Solution Explorer and Schema View Object Name Resolution Referencing the Database Name in Object Definitions Object-level SET Options Object Validation
Add objects to your database or server project: You can add objects to your database or server project by using any of several methods. You can import objects from a script or from an updated database or server. You can create objects in the default schema or in a specific schema. You can add files that contain object definitions to your project. You can customize the object-level options (known as SET options) after you add the objects to your project.	How to: Import Database Objects from a Script How to: Import Updates from a Database into the Database Project How to: Create a Database Object How to: Add Database Objects to a Specific Schema How to: Add Existing Items to a Database Project How to: Specify Object-level SET Options
Add specialized objects to your database or server project: To add some object types to your project, you must follow a slightly different procedure that is specific to that type of object. You can define table and index options. You can add a user to a role. You can define full-text catalogs. You can define filegroups and files.	How to: Specify Table and Index Options How to: Add a User to a Role How to: Define Full-text Catalogs Overview of Files and Filegroups How to: Add Files and Filegroups
Modify database or server objects: You can modify the definitions of the objects in your server or database project. You can also modify the object-level SET options for those objects.	How to: View Database Objects How to: Modify Database Objects How to: Specify Object-level SET Options
Remove objects from your database or server project: You can delete objects from your database or server project to remove them permanently. You can also temporarily exclude objects that are still under development.	How to: Delete Database Objects How to: Exclude Files from a Database Project
Customize the templates for database and server objects: You can view and modify the templates that you use when you create database and server objects.	An Overview of Templates for Database and Server Objects
Troubleshoot problems: You can learn more about how to troubleshoot common problems with database and server projects.	Troubleshooting Database Project, Build, and Deployment Issues

Object Definitions

Database and server 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 and parameters to a stored procedure or function. Columns are specified in the file that contains the table definition, and parameters are specified in the file that contains the stored procedure or function.

The Build Action property for a .sql file indicates whether the file is parsed to verify whether it contains the definition of a database object. By default, files that contain definitions for database objects 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 build script includes the file.

The name of each object determines its file name, and the type of object determines its default file name extension. For example, functions are in files that are named ObjectName.function.sql, and stored procedures are in files that are named ObjectName.proc.sql.

Objects in Solution Explorer and Schema View

In Solution Explorer, you work with the files that your project contains and that contain the definitions for your objects. You cannot use rename refactoring in Solution Explorer. You use Solution Explorer when you want to work with files, such as when you want to check your project in to version control.

In Schema View, you work with the database objects. You use Schema View when you are working with the objects and not the files that contain their definitions. For example, you can use Schema View to create database objects.

Object Name Resolution

In general, object names should be resolved by using the same rules that SQL Server uses. Some sysobjects might need to be fully qualified to resolve correctly. If errors appear or you 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 an object definition script in a database project, but the following restrictions apply:

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

  [$(databasename)]

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

  [$(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. By default, the SET options for each object match the database project's SET options. You can set these two options to Project default, On, or Off. When you build the database project, only those object-level SET options that are set to a different value from 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.

Object Validation

Validation occurs when you save changes to a database object that you created or modified. 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

If a database object is not supported in the release that you are using, the icon for that object is an inverted yellow triangle.

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

Related Scenarios

• Scenario: Create and Modify Database Scripts
  You can specify pre-deployment and post-deployment scripts to perform actions before or after you deploy a database. For example, you might use the post-deployment script to add reference data to a lookup table.

• Refactor Database Code and Data
  You can use database refactoring to eliminate some repetitive and error-prone tasks, such as renaming all references to an object, fully qualifying names, expanding wildcard characters, or moving an object to a different schema.

• Build and Deploy Databases to an Isolated Development Environment
  After you modify objects in your project, you build and deploy the changes to your isolated development environment.

• Improving Database Code with Static Analysis
  You can analyze your database code to find and fix common design, naming, and performance issues.

See Also

Concepts

Build and Deploy Databases to a Staging or Production Environment

Build and Deploy Databases to an Isolated Development Environment

Rename All References to a Database Object

Performing Iterative Database Development

Terminology Overview of Database Edition