Contained DB Collation error

The MSDN page on Contained Database Collations has some guidance which includes:

• In a contained database, the catalog collation Latin1_General_100_CI_AS_WS_KS_SC. This collation is the same for all contained databases on all instances of SQL Server and cannot be changed.

So, your problem is with the catalog collation. As you change to contained, it is changing the database's catalog collation to Latin1_General_100_CI_AS_WS_KS_SC which is the source of your problem.

Perhaps the comments on collation, particulary the CATALOG_DEFAULT may provide you some assistance:

• The database collation is retained, but is only used as the default collation for user data.
• A new keyword, CATALOG_DEFAULT, is available in the COLLATE clause. This is used as a shortcut to the current collation of metadata in both contained and non-contained databases.

Crossing Between Contained and Uncontained Contexts

• As long as a session in a contained database remains contained, it must remain within the database to which it connected. In this case the behavior is very straightforward. But if a session crosses between contained and non-contained contexts, the behavior becomes more complex, since the two sets of rules must be bridged.

And this link ends with the Conclusion:

• The collation behavior of contained databases differs subtly from that in non-contained databases. This behavior is generally beneficial, providing instance-independence and simplicity. Some users may have issues, particularly when a session accesses both contained and non-contained databases.

Regarding Data Collation Issues:

If you also have to resolve collation problems on the data by using COLLATE DATABASE_DEFAULT. Likely your two databases have the same collation for the data. But if not, you can use the following technique:

select NameValue COLLATE DATABASE_DEFAULT from MyDatabase.Schema.Table
EXCEPT
select NameValue COLLATE DATABASE_DEFAULT from TheirDatabase.Schema.Table

The value of this approach is that you do not need to specify a particular collation, but COLLATE DATABASE_DEFAULT allows you to use the collation of the current database. This would resolve data collation issues.

The issue you are seeing is a conflict between the collation of the metadata in the system Views -- sys.foreign_keys and sys.objects -- and the table variable @DependencyTree.

As pointed out in @RLF's answer, the collation of database metadata changes from DATABASE_DEFAULT (in your case Latin1_General_CI_AS) to CATALOG_DEFAULT (always Latin1_General_100_CI_AS_WS_KS_SC) when altering the database to be "contained". This affects the name fields being returned in this query:

SELECT fk.object_id AS [ForeignKeyObjectID], fk.name AS [ForeignKeyObjectName],
       fk.referenced_object_id AS [ParentTableID], parent.name AS [ParentTableName],
       fk.parent_object_id AS ChildTableID, child.name AS [ChildTableName], @Generation
FROM   @DependencyTree dt
INNER JOIN sys.foreign_keys fk
        ON fk.referenced_object_id = dt.ChildTableID
INNER JOIN sys.objects parent
        ON fk.referenced_object_id = parent.[object_id]
INNER JOIN sys.objects  child
        ON fk.parent_object_id = child.[object_id]

EXCEPT

SELECT ForeignKeyObjectID, ForeignKeyObjectName,
       ParentTableID, ParentTableName,
       ChildTableID, ChildTableName, @Generation
FROM   @DependencyTree

The fk.name, parent.name, and child.name fields are all initially collated as Latin1_General_CI_AS but then change to Latin1_General_100_CI_AS_WS_KS_SC when you ALTER the database to make it "contained".

The error is being thrown because the string fields in both parts of the EXCEPT need to have matching collations. But the other part of the EXCEPT is using the table variable which is defined as:

DECLARE @DependencyTree as Table(ForeignKeyObjectID INT,
     ForeignKeyObjectName NVARCHAR(MAX), ParentTableID INT, ParentTableName NVARCHAR(MAX),
ChildTableID INT, ChildTableName NVARCHAR(MAX), Generation INT)

No collations are specified for the NVARCHAR(MAX) fields (which technically should be declared as sysname -- always all lower-case for that one -- since that is the datatype of the source system Views of sys.objects and sys.foreign_keys). While it is not mentioned in the Contained Database Collations Table MSDN page, unlike temporary tables, table variables get their default collation from the database, not from tempdb (which is why you didn't see this error in the past since your tempdb collation should be SQL_Latin1_General_CP1_CI_AS since that is the instance collation; you would have gotten this error before if this table were a temporary table). So the collation used for the ForeignKeyObjectName, ParentTableName, and ChildTableName fields was Latin1_General_CI_AS and will still be that same collation upon the database being "contained".

Changing that table variable declaration to be the following should resolve this issue:

DECLARE @DependencyTree Table
(
  ForeignKeyObjectID INT,
  ForeignKeyObjectName sysname COLLATE CATALOG_DEFAULT,
  ParentTableID INT,
  ParentTableName sysname COLLATE CATALOG_DEFAULT,
  ChildTableID INT,
  ChildTableName sysname COLLATE CATALOG_DEFAULT,
  Generation INT
);

Using COLLATE CATALOG_DEFAULT will work with databases when they are not contained and when they are altered to be contained since CATALOG_DEFAULT resolves to the database default in non-contained databases. Another way of stating this behavior is that since database metadata is collated as CATALOG_DEFAULT in either state of the database, it will work in the table variable (and temporary tables) in either state of the database.