SQL Server 2008R2 convert collation

I have an existing database that uses the Arabic_CI_AS collation, but I need to convert it to SQL_Latin1_General_CP1_CI_AS the default collation for the server, but the problem is when I use the following command:

USE master;
GO

ALTER DATABASE MyDB
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

It give me the following error message:


Msg 5075, Level 16, State 1, Line 1

The object 'GetAllSuppliersInCompliance' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.


Msg 5075, Level 16, State 1, Line 1

The object 'GetTier1SupplierComplianceStatus' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
.....


Msg 5072, Level 16, State 1, Line 1

ALTER DATABASE failed. The default collation of database 'MyDB' cannot be set to SQL_Latin1_General_CP1_CI_AS.

So I tried the following to disable the constraints temporary using the following command:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

I then executed the first command, however the same error appeared.

Assuming those objects are stored procedures or views (and not tables), you'll need to:

• script the objects in question

• drop them from the database (or just remove the schema-binding)

• change the database collation

• recreate the objects by running the scripts you created in the first step.

As an aside, you didn't need to sp_msforeachtable "ALTER TABLE..."

StackExchange Database Administrators Q#71856, answer score: 7