Creating and Modify Database and Server Objects

  • Article

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.

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

Understand dependencies between database objects: You can view the objects on which a database object depends and the objects that depend on that object.

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.

  • You can specify permissions for your database objects.

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.

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.

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.

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.

Troubleshoot problems: You can learn more about how to troubleshoot common problems with database and server projects.

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 if 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.

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

Writing and Changing Database Code