SSDT (the database tools one) enforces a 2-part naming convention when referencing objects within a project. For example, a view might look something like this:
CREATEVIEW dbo.SomeView AS
Note that the customers table above is referred to by [Schema].[Table]. When I start up a new SSDT project from an existing database, I often find a number of 3-part references sprinkled throughout the code. That is, the view above would read:
In which case, SSDT will throw the following warnings:
SQL71562: View: [dbo].[SomeView] has an unresolved reference to object [SomeDatabase].[dbo].[SomeView].[Customer_Id]
SQL71562: View: [dbo].[SomeView] has an unresolved reference to object [SomeDatabase].[dbo].[SomeView].[Customer_Name]
Removing the reference to the database fixes these problems. However, there are a couple good reasons why SSDT imposes a 2-part naming convention in the first place:
·A 3-part name is unnecessary (despite what SSMS might spit out with “Script Table As > Select To”)
Explicitly naming the database hardcodes the database name in the definition of the object.
oSo? This means that you cannot deploy working code to a database of a different name.
oWhy would I ever do that? How about for parallel testing or branching? Or for bringing the definition of production back to Dev and have it live side by side, without overlaying dev. This is an excellent way for troubleshooting a production problem.
When you actually do need to reference an external database, this can be done using database references, which utilize SQLCMD variables for their name. Similarly, the database name can be changed at the time of deployment to aid in testing and development.