VS2010 Database Development Tools dependency resolution

Today, I am talking about how VS2010 Database Development Tools resolved a potential “Catch22” dependency resolution. To start with, here is the problem:

I have a database that has dependencies on another database. I will call then “Source” and “Main” database. “Main” database being the one that has dependencies on the “Source” database. Since I did not like to have explicit reference to “Source” database in my “Main’ database, I created synonyms for all objects that needed to be referred into the source database.

So, I have synonyms that go like this:

CREATE
SYNONYM
[Main].[DimCustomer]
FOR
[$(AdvWorkLt)].[DW].[DimCustomer];

CREATE
SYNONYM
[Main].[DimProduct]
FOR
[$(AdvWorkLt)].[DW].[DimProduct];

…

 

The [$(AdvWorkLt)] is a SQLCMD variable in my database project that points to my Source database:

Next, I created views on top of these synonyms:

CREATE
VIEW
[dbo].[DimCustomer]

    AS
SELECT
*
from
[Main].[DimCustomer]

 

Since I did not like to do a “Select *” in my view, I used the refactoring capabilities of Database Development Tools to expand the wildcard:

The refactoring action gave me the following SQL:

CREATE
VIEW
[dbo].[DimCustomer]

    AS
SELECT
[Main].[DimCustomer].[IDCustomer],

     [Main].[DimCustomer].[CustomerID],

             [Main].[DimCustomer].[Title],

             [Main].[DimCustomer].[FirstName],

             [Main].[DimCustomer].[MiddleName],

             [Main].[DimCustomer].[LastName],

             [Main].[DimCustomer].[Suffix],

             [Main].[DimCustomer].[CompanyName],

             [Main].[DimCustomer].[ADWSalesPerson],

             [Main].[DimCustomer].[EmailAddress],

             [Main].[DimCustomer].[Phone],

             [Main].[DimCustomer].[CustomerEffDate],

             [Main].[DimCustomer].[CustomerEndDate],

             [Main].[DimCustomer].[CustomerCurrent],

             [Main].[DimCustomer].[LoadID]

FROM
[Main].[DimCustomer]

 

Now, I questioned how the project would looks like when it would create a deployment script. To be honest, I had some doubts on how Database Development Tools would be able to render the dependencies between the synonyms and the views. I was in for a pleasant surprise because the dependencies has been resolved perfectly in the resulting deployment script:

PRINT
N’Creating [Main].[DimCustomer]…’;

 

 

GO

CREATE
SYNONYM
[Main].[DimCustomer]
FOR
[$(AdvWorkLt)].[DW].[DimCustomer];

 

 

GO

PRINT
N’Creating [dbo].[DimCustomer]…’;

 

 

GO

CREATE
VIEW
[dbo].[DimCustomer]

    AS
SELECT
[Main].[DimCustomer].[IDCustomer],

     [Main].[DimCustomer].[CustomerID],

             [Main].[DimCustomer].[Title],

             [Main].[DimCustomer].[FirstName],

             [Main].[DimCustomer].[MiddleName],

             [Main].[DimCustomer].[LastName],

             [Main].[DimCustomer].[Suffix],

             [Main].[DimCustomer].[CompanyName],

             [Main].[DimCustomer].[ADWSalesPerson],

             [Main].[DimCustomer].[EmailAddress],

             [Main].[DimCustomer].[Phone],

             [Main].[DimCustomer].[CustomerEffDate],

             [Main].[DimCustomer].[CustomerEndDate],

             [Main].[DimCustomer].[CustomerCurrent],

             [Main].[DimCustomer].[LoadID]

FROM
[Main].[DimCustomer]

GO

 

In conclusion, VS2010 Database Deployment Tools is able to resolve object dependencies gracefully. I was a bit skeptical about it but now, I am convinced more than ever that this is the best tool to manage databases.