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.
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.
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.
Add objects to your database or server project: You can add objects to your database or server project by using any of several methods.
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.
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.
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.
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.
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.
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:
You cannot use the token to specify a three-part name as the following example shows:
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
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.
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.
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.
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.