Return to Answer

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:

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:

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 probably 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). 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".

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".