How to change the collation of all the existing objects at once inside a database after upgrading to SQL 2005?

  • Article

Consider the following the scenario

Scenario :

SQL Server 2000 Instance : Collation A

We perform a upgrade from SQL Server 2000 to SQL Server 2005 changing the collation.

After successful upgrade ,

SQL Server 2005 Instance : Collation B

2005 Databases : Collation B

Objects inside 2005 databases : Collation A 

Now this is what we would like to change to Collation B (New collation as that of new instance) as we had hundreds of such databases with thousands of objects.

Export/import will not work as it doesn’t preserve the identity column values. In other method , we couldn’t export primary keys.

Following are the steps which can be used to change the collation of all the existing objects

1. Script out the Source database for all objects using the "Generate Script" wizard (without collation) .

2. Create a NewDatabase (Destination db) and run the generated script .

3. BCP out the data from the source database. Run the below script on the source database to generate the BCP OUT commands for all objects. Copy paste the ouput to a batch file and run the batchfile .

select 'bcp.exe <SourceDatabaseName>.dbo.'+ name +' out c:\'+name+'.bak -S<SQLServerName\Instancename> -T -c -E' from sysobjects where xtype='U'

4. Once the BCP OUT is complete for all the tables, execute the below to generate BCP IN commands .

select 'bcp.exe <DestinationDatabaseName>.dbo.'+ name +' in c:\'+name+'.bak -S<SQLServerName\Instancename> -T -c -E' from sysobjects where xtype='U'

Copy and Paste the ouput to a batch file and run the batchfile. The identity values will be preserved as per the KB article

https://support.microsoft.com/?id=952111

P.S. : This is a workaround for a specific kind of scenario as mentioned above.

 

Prashant Kumar,
SE, Microsoft SQL Server