How to change AdventureWorks database collation on SQL Server 2005?

The challenge is to change the collation for the AdventureWorks database from Latin1_General_CS_AS to SQL_Latin1_General_CP1_CI_AS. Does anyone know how to do it?

I tried this:

alter database AdventureWorks set single_user
alter database AdventureWorks collate SQL_Latin1_General_CP1_CI_AS
alter database AdventureWorks set multi_user

But I got following error:

Msg 5075, Level 16, State 1, Line 3

The object 'ufnLeadingZeros' 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 3

The object 'CK_ProductReview_Rating' 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 3

The object 'CK_TransactionHistory_TransactionType' 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 3

The object 'CK_ProductVendor_AverageLeadTime' 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.

Then I was trying something like this:

DECLARE @NewCollation VARCHAR(255), @DBName sysname

SELECT @NewCollation = 'SQL_Latin1_General_CP1_CI_AS', -- change this to the collation that you need
       @DBName = DB_NAME()

DECLARE @CName varchar(255), 
        开发者_Go百科@TbleName sysname, 
        @objOwner sysname, 
        @Sql varchar(8000), 
        @Size int, 
        @Status tinyint, 
        @Colorder int

Declare CurWhileLoop cursor read_only forward_only local
for Select
       QUOTENAME(C.Name)
      ,T.Name
      ,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name)
      ,C.Prec
      ,C.isnullable
      ,C.colorder
    From syscolumns C
      inner join systypes T on C.xtype=T.xtype
      inner join sysobjects O on C.ID=O.ID
      inner join sysusers u on O.uid = u.uid
    where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')
      and O.xtype in ('U')
      and C.collation != @NewCollation
    and objectProperty(O.ID, 'ismsshipped')=0
    order by 3, 1

open CurWhileLoop
SET XACT_ABORT ON
begin tran
fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder
while @@FETCH_STATUS =0
begin
  set @Sql='ALTER TABLE '+@objOwner+' ALTER COLUMN '+@CName+' '+@TbleName+ isnull ('('
+convert(varchar,@Size)+')', '') +' COLLATE '+ @NewCollation
+' '+case when @Status=1 then 'NULL' else 'NOT NULL' end
  exec(@Sql) -- change this to print if you need only the script, not the action
  fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder
end
close CurWhileLoop
deallocate CurWhileLoop
commit tran

And I got following error:

Msg 4104, Level 16, State 1, Line 10

The multi-part identifier "U.Name" could not be bound.

I was trying to run this last query against AdventureWorks database and I got U.Name name error as above, and when I was trying to run this query against master database nothing happens.

Please help!

Suggest starting over from scratch. It will take less effort for you compared to your approach above.

Download a new copy / scripts for SQL Server 2005 AdventureWorks.

Ensure your server has the collation you want by default. Right click-Properties->General->Server Collation.

When you create your database with SQL Server Management Studio, you can explicitly choose the collation.