How do I configure Schema Compare to produce separate files for foreign keys?

Question

To work with our database project in VS 2010, we make schema changes directly into our local project database using SSMS, then when we are ready to check in we do a Schema Compare, the local database vs the project, which identifies our changes. Then when we Write Changes, it alters or creates schema object scripts into our database project. If all looks well, we can then check in those changes to TFS.

Our standard on foreign keys and indices is to have those saved separately. That is, even though I define a column in a new table by saying something like this:

CREATE TABLE Billing.EntryPointProduct
(
    EntryPointProductId INT IDENTITY(1,1) PRIMARY KEY,
    EntryPointId INT FOREIGN KEY REFERENCES Billing.EntryPoint(EntryPointId),
    ProductId INT FOREIGN KEY REFERENCES ProductCatalog.Product(ProductID)
)

What we really want, in the end, is a file for the EntryPointProduct table and a file for each of the Foreign Key objects. However, right now the schema compare is producing it all in one table script. I swear I have done this before with schema compare, but I can't seem to find the way to configure it to do this. Can anyone advise?

score 0 · Accepted Answer · answered Jun 07 '12 at 01:45

Can you change your DDL so it looks like this:

CREATE TABLE Billing.EntryPointProduct
(
    EntryPointProductId INT IDENTITY(1,1),
    EntryPointId INT,
    ProductId INT,
CONSTRAINT [PK_EntryPointProduct] PRIMARY KEY CLUSTERED (EntryPointProductId)
)

ALTER TABLE Billing.EntryPointProduct
WITH CHECK ADD CONSTRAINT FK_EntryPointProduct_EntryPoint FOREIGN KEY(EntryPointId)  REFERENCES Billing.EntryPoint(EntryPointId)

ALTER TABLE Billing.EntryPointProduct
WITH CHECK ADD CONSTRAINT FK_EntryPointProduct_ProductCatalog FOREIGN KEY(ProductId)  REFERENCES ProductCatalog.Product(ProductID)

That way you'd have 3 different files, and your FK's would have real names (FK_*) instead of system-generated names which will be randomly generated each time they are created and therefore won't match if you did a schema compare between 2 separately scripted out databases. (Same reason why I modified the PK code)

That definitely is the way we want the finals to come out. I had been hoping that by inputting it my way, then doing the schema compare, that it would generate these multi-line versions on its own. Your point about the FK names also makes good sense. I will experiment with this today. — Mike K, Jun 18 '12 at 16:47
Good call Levi, that did the trick. It's a pain to define them separately like this, but the schema compare correctly sets them into their own files, and I get the benefit of a non-cryptic name. The keys that I left defined in the table definition show up in the compare results as "On table:" instead of with a filename. — Mike K, Jun 18 '12 at 21:28

How do I configure Schema Compare to produce separate files for foreign keys?

1 Answers1