Your dependencies will probably be completely out of whack, but I haven't thoroughly tested this. I do know that in this scenario:
CREATE SCHEMA blat AUTHORIZATION dbo;
GO
CREATE TABLE dbo.foo(a INT PRIMARY KEY);
CREATE TABLE dbo.bar(a INT FOREIGN KEY REFERENCES dbo.foo(a));
GO
CREATE PROCEDURE dbo.pX AS
BEGIN
SET NOCOUNT ON;
SELECT a FROM dbo.bar;
END
GO
CREATE VIEW dbo.vFooBar
AS
SELECT foo.a, bar.a AS barA
FROM dbo.foo
INNER JOIN dbo.bar
ON foo.a = bar.a;
GO
ALTER SCHEMA blat TRANSFER dbo.foo;
ALTER SCHEMA blat TRANSFER dbo.bar;
ALTER SCHEMA blat TRANSFER dbo.pX;
ALTER SCHEMA blat TRANSFER dbo.vFooBar;
The foreign keys actually migrate more smoothly than I expected (with the caveat that I'm testing on a much more recent version than you). But because the code in blat.pX
still references dbo.bar
, obviously executing the procedure:
EXEC blat.pX;
Is going to yield this error:
Msg 208, Level 16, State 1, Procedure pX
Invalid object name 'dbo.bar'.
And dependency queries, such as:
SELECT * FROM sys.dm_sql_referenced_entities('blat.pX', N'OBJECT');
Will yield this error:
Msg 2020, Level 16, State 1
The dependencies reported for entity "blat.pX" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.
And querying the view:
SELECT a, barA FROM blat.vFooBar;
Yields these errors:
Msg 208, Level 16, State 1, Procedure vFooBar
Invalid object name 'dbo.foo'.
Msg 4413, Level 16, State 1
Could not use view or function 'blat.vFoobar' because of binding errors.
So, this could involve a lot of cleanup. And after you've fixed all the object references, you'll probably want to recompile all of the modules and refresh all of the views in the new schema. You can generate a script for that quite similar to the example above.
Best Answer
3 ways you can do this.
Then you can deselect foreign keys.
In my opinion this the best solution for you as this will always have latest schema but I doubt if SQL 2008 R2 can create a solution with new SSDT. General idea is once you create a solution you can delete/modify objects (FK in this case) and rebuild the project. Once you clear up FK you can deploy.
Create an empty database. Script out tables without FK and then run the script against empty database in cloud. In SSMS right click the database, go to task-->generate script...-->set scripting options. Then set foreign key to false.