Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Script to Change Collation of User Database Expand / Collapse
Author
Message
Posted Wednesday, November 18, 2009 3:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:50 AM
Points: 32, Visits: 471
Hello,

Is their is any Script to change collation of user database ?
The process Which I know;

First Backup the User database

1.creation of new database with required collation (create database..with collate clause)
2.Then use of Import & export functionality to move data to newly created database
3.Drop the old database
4.Rename the newly created database.

Wheather this method is the only way ? or any suggestion.

Rd,
Deepali

Post #820675
Posted Wednesday, November 18, 2009 9:17 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:22 AM
Points: 3,433, Visits: 14,428
lokhande.deepali (11/18/2009)
Hello,

Is their is any Script to change collation of user database ?
The process Which I know;

First Backup the User database

1.creation of new database with required collation (create database..with collate clause)
2.Then use of Import & export functionality to move data to newly created database
3.Drop the old database
4.Rename the newly created database.

Wheather this method is the only way ? or any suggestion.

Rd,
Deepali




In SQL Server 2005 SP2 and up you can manually change the database collation in the database properties and do the same on the tables in design mode. If you have more than 1000 tables then you could use a script. I am assuming you know the whole server require master rebuild.



Kind regards,
Gift Peddie
Post #820932
Posted Wednesday, November 18, 2009 9:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:50 AM
Points: 32, Visits: 471
--I am assuming you know the whole server require master rebuild

Yes , I know that to rebuid require when system databases collation has to be changed.


I want script which can change multiple user databases collation at a single execution.
Post #821347
Posted Wednesday, November 18, 2009 10:05 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:22 AM
Points: 3,433, Visits: 14,428
lokhande.deepali (11/18/2009)
--I am assuming you know the whole server require master rebuild

Yes , I know that to rebuid require when system databases collation has to be changed.


I want script which can change multiple user databases collation at a single execution.


I would not do that because it is not practical but here are two threads that may help.

http://www.sqlservercentral.com/Forums/Topic489706-146-1.aspx

http://www.sqlservercentral.com/Forums/Topic483920-146-1.aspx



Kind regards,
Gift Peddie
Post #821358
Posted Friday, November 20, 2009 2:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:48 AM
Points: 430, Visits: 312
Must get round to writing this as an articel some time.

This document assumes that the SERVER is at the correct collation and that the DATABASE is a different collation to the server. These steps will bring the Database into alignment. It is for SQL 2005 ONLY and make sure you have a backup of the database before you start.

1. load the script called Generate Change Column Collation
Ensure that the Collation setting at the top is correct
Run it against the Database
Save the results as 01_Change_Column_Collation.SQL

2. Load the Script called Generate Primary Key Constraints
Run it against the database
Save the results as 02_Create_PK.SQL

3. Load the Script called Generate Alternate Key Indexes
Run it against the database
Save the results as 03_Create_AK.SQL

4. Load the Script called Generate Foreign Key Constraints
Run it against the database
Save the results as 04_Create_FK.SQL

5. Load the Script called Generate Check Constraints
Run it against the database
Save the results as 05_Create_CK.SQL

6. Load the Script called Drop Check Constraints
Run it against the database

7. Load the Script called Drop Foreign Key Constraints
Run it against the database

8. Load the Script called Drop Alternate Key Indexes
Run it against the database

9. Load the Script called Drop Primary Key Constraints
Run it against the database

10. enter the following commands
USE MASTER
ALTER DATABASE xxxxx COLLATE xxxxxxxxxxx

11. Load the Script called 01_Change_Column_Collation.SQL
Run it against the database

12. Load the Script called 02_Create_PK.SQL
Run it against the database

13. Load the Script called 03_Create_AK.SQL
Run it against the database

14. Load the Script called 04_Create_FK.SQL
Run it against the database

15. Load the Script called 05_Create_CK.SQL
Run it against the database


AND NOW THE SCRIPTS

Generate Change Column Collation
declare @toCollation sysname

SET @toCollation = 'Latin1_General_CI_AS' -- Database default collate

SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
WHEN DATA_TYPE in ('text','ntext') then ''
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
+' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'

END
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
and COLLATION_NAME <> @toCollation

Generate_Primary_Key_Contraints

BEGIN TRAN

-- Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT so.name,si.name,si.type_desc
from sys.indexes si
join sys.objects so
on si.object_id = so.object_id
and so.type = 'U'
where si.type_desc <> 'HEAP'
and si.is_Primary_Key = 1
ORDER BY so.Name

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
Declare @KeyType nvarchar(50)

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName,@KeyType
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY ' + @KeyType + ' ('

-- Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName
ORDER BY ORDINAL_POSITION
OPEN cPKColumn

DECLARE @PkColumn SYSNAME
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
-- Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
SET @PKSQL = @PKSQL + ', '

SET @PKSQL = @PKSQL + @PkColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ')'
-- Print the primary key statement
PRINT @PKSQL

FETCH NEXT FROM cPK INTO @PkTable, @PkName, @KeyType
END
CLOSE cPK
DEALLOCATE cPK


ROLLBACK

Generate_Alternate_Key_Indexes
BEGIN TRAN

-- Get all existing primary keys
DECLARE cPK CURSOR FOR
select si.Object_id,si.Index_Id,so.name,si.name,si.type_desc,si.is_unique from sys.indexes si
join sys.objects so
on so.object_id = si.object_id
and so.type = 'U'
and si.is_Primary_key = 0
and si.type_desc <> 'HEAP'
order by so.name

Declare @ObjectID int
Declare @IndexID int
Declare @TableName nvarchar(50)
Declare @IndexName nvarchar(50)
declare @IndexType nvarchar(50)
declare @IndexUnique bit

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
Declare @KeyUnique nvarchar(10) set @KeyUnique = ''
if @IndexUnique = 1 set @KeyUnique = 'Unique'
SET @PKSQL = 'Create ' + @KeyUnique + ' ' + @IndexType + ' INDEX ' + @IndexName + ' ON ' + @TableName + ' ('

-- Get all columns for the current key
DECLARE cPKColumn CURSOR FOR
select sc.name
from sys.index_Columns sic
join sys.columns sc
on sc.object_id = sic.object_id
and sc.column_id = sic.column_id
where sic.object_id = @ObjectID
and sic.Index_id = @IndexID

OPEN cPKColumn

DECLARE @PkColumn SYSNAME
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
-- Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
Begin
SET @PKSQL = @PKSQL + ', '
end

SET @PKSQL = @PKSQL + @PkColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ')'
-- Print the primary key statement
PRINT @PKSQL

FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique
END
CLOSE cPK
DEALLOCATE cPK


ROLLBACK

Generate Foreign Key Constraints
BEGIN TRAN

-- Get all existing primary keys
DECLARE cPK CURSOR FOR
select sf.object_id,sf.name,so.name,sor.name from sys.foreign_keys sf
join sys.objects so
on so.object_id = sf.parent_object_id
join sys.objects sor
on sor.object_id = sf.referenced_object_id
ORDER BY sf.Name

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
Declare @RefName nvarchar(50)
declare @objectid bigint

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
Declare @FKSQL Nvarchar(4000) set @fkSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' WITH NOCHECK ADD CONSTRAINT ' + @PkName + ' Foreign KEY ' + ' ('
Set @FKSQL = ' REFERENCES ' + @RefName + ' ('

-- Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
select so.name,sor.name from sys.foreign_key_columns sfc
join sys.columns so
on so.column_id = sfc.parent_column_id
and so.object_Id = sfc.parent_object_id
join sys.columns sor
on sor.column_id = sfc.referenced_column_id
and sor.object_id = sfc.referenced_object_id
where sfc.Constraint_object_id = @ObjectID
OPEN cPKColumn

DECLARE @PkColumn SYSNAME
Declare @fkColumn sysname
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
-- Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn,@fkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
Begin
SET @PKSQL = @PKSQL + ', '
set @FkSQL = @FKSQL + ', '
end

SET @PKSQL = @PKSQL + @PkColumn
set @FkSql = @FKSQL + @FKColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn,@FKColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ')'
set @FKSql = @FKSQL + ')'
-- Print the primary key statement
PRINT @PKSQL
Print @FKSQL

FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName
END
CLOSE cPK
DEALLOCATE cPK


ROLLBACK

Generate Check Constraints
select 'Alter Table ' + st.name + ' With Nocheck ' + 'Add Constraint ' + scc.name + ' check ' + scc.definition
from sys.tables st
join sys.check_constraints scc
on st.object_id = scc.parent_object_id
order by st.name

Drop Check Constraints
declare ca Cursor
for select st.name,scc.name
from sys.tables st
join sys.check_constraints scc
on st.object_id = scc.parent_object_id
order by st.name

declare @TableName nvarchar(50)
declare @ConstraintName nvarchar(50)
declare @DbName nvarchar(50)
Declare @Sql nvarchar(4000)

set @dbName = db_name()
open ca
fetch from ca into @TableName,@ConstraintName
While @@Fetch_Status = 0
Begin
set @SQL = 'use ' + db_name() +' Alter Table ' + @TableName + ' Drop Constraint ' + @ConstraintName + ';'
print @sql
exec (@Sql)
fetch from ca into @TableName,@ConstraintName
end

close ca
deallocate ca
Drop Foreign Key Constraints
-- Get all existing Foreign keys
DECLARE cPK CURSOR FOR
select sf.object_id,sf.name,so.name,sor.name from sys.foreign_keys sf
join sys.objects so
on so.object_id = sf.parent_object_id
join sys.objects sor
on sor.object_id = sf.referenced_object_id
ORDER BY sf.Name

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
Declare @RefName nvarchar(50)
declare @objectid bigint

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
Declare @FKSQL Nvarchar(4000) set @fkSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' Drop CONSTRAINT ' + @PkName

-- Print the Drop key statement
PRINT @PKSQL
Exec(@pksql)

FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName
END
CLOSE cPK
DEALLOCATE cPK


Drop Alternate Key Indexes

-- Get all existing Alternate keys
DECLARE cPK CURSOR FOR
select si.Object_id,si.Index_Id,so.name,si.name,si.type_desc,si.is_unique from sys.indexes si
join sys.objects so
on so.object_id = si.object_id
and so.type = 'U'
and si.is_Primary_key = 0
and si.type_desc <> 'HEAP'
order by so.name

Declare @ObjectID int
Declare @IndexID int
Declare @TableName nvarchar(50)
Declare @IndexName nvarchar(50)
declare @IndexType nvarchar(50)
declare @IndexUnique bit

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
Declare @KeyUnique nvarchar(10) set @KeyUnique = ''
if @IndexUnique = 1 set @KeyUnique = 'Unique'
SET @PKSQL = 'DROP INDEX ' + @IndexName + ' ON ' + @TableName

-- Print the Alternate key statement
PRINT @PKSQL
exec (@pksql)
FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique
END
CLOSE cPK
DEALLOCATE cPK




Drop Primary Key Constraints

-- Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Where Constraint_Type = 'Primary Key'
ORDER BY TABLE_NAME

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
SET @PKSQL = 'use ' + db_name() + ' ALTER TABLE ' + @PkTable + ' drop CONSTRAINT ' + @PkName

print @PKSQL
exec(@PKSQL)

FETCH NEXT FROM cPK INTO @PkTable, @PkName
END
CLOSE cPK
DEALLOCATE cPK

Post #822173
Posted Friday, November 20, 2009 4:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:50 AM
Points: 32, Visits: 471
Hi Paul,

Thanks..

I will try this script.

Rd,
Deepali
Post #822241
Posted Friday, November 20, 2009 7:46 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:22 AM
Points: 3,433, Visits: 14,428
Must get round to writing this as an articel some time. [BigGrin]

This document assumes that the SERVER is at the correct collation and that the DATABASE is a different collation to the server. These steps will bring the Database into alignment. It is for SQL 2005 ONLY and make sure you have a backup of the database before you start.



That is almost funny you are going to write an article about how to change collation in a database a task not related to INDEXES with a Cursor code going through Microsoft system database tables. That means your code may be doing what is needed to rebuild the Master and I am not so sure it is valid. I do this all the time including changing the Northwind sample database to SQL Server 2008 and Unicode encoded. Check below for the manual way which is how people who work in many languages do it. I also understand people want short cuts but the valid way to do that is to rebuild the Master.

http://msdn.microsoft.com/en-us/library/bb330962(SQL.90).aspx


Kind regards,
Gift Peddie
Post #822410
Posted Friday, November 20, 2009 9:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 11:45 AM
Points: 169, Visits: 282
Just change the collation of the database using Alter Database statement.

This should change the collation of all Tables and columns to the new collation unless

there is an explicity specification of collation on Tables or columns. If any of the columns in a table has a different collation the ALTER Database for changing the collation will not change the collation of all other columns in the table (it will be the same as before).

Also keep in mind that when you do a export and import from on database to a different database with differnet collation, ALL THE TABLES AND COLUMNS WHICH GETS CREATED IN THE DESTINATION DATABASE (WITH DIFFERENT COLLATION) WILL FOLLOW THE COLLATION OF THE DESTINATION DATABASE. NOT THE ONE IN THE SOURCE DATABASE EVEN FOR COLUMNS WITH EXPLICIT COLLATION

One more thing which is important: even if you could get through all the above hurdles, the change in collation may lead to a different interpretation of the the data for data types like char, varchar and text.



subban
Post #822507
Posted Friday, November 20, 2009 10:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:48 AM
Points: 430, Visits: 312
Dear All

Maybe 2008 has a mechanism for doing this but 2005 certainly does not.

These scripts All assume that the Collation of the SERVER is set to what you need it to be. All they do is change the collation of every text column on every table in the database that you run it against to the collation of the server.

In order to do that you have to remove any Indexes, FK Constraints, PK Constraints and Check Constraints. The 1st 5 Scripts Just Generate the commands that you will need to put them back afterwards. That is why there are cursors on system tables.

These scripts have been proved time and time again when taking Databases that have been installed by 3rd party vendors that do not match my collation. The worst of which was a Dynamics GP 10 Database.

Paulus
Post #822526
Posted Friday, November 20, 2009 10:26 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:22 AM
Points: 3,433, Visits: 14,428
Paul Smith-221741 (11/20/2009)
Dear All

Maybe 2008 has a mechanism for doing this but 2005 certainly does not.

These scripts All assume that the Collation of the SERVER is set to what you need it to be. All they do is change the collation of every text column on every table in the database that you run it against to the collation of the server.

In order to do that you have to remove any Indexes, FK Constraints, PK Constraints and Check Constraints. The 1st 5 Scripts Just Generate the commands that you will need to put them back afterwards. That is why there are cursors on system tables.

These scripts have been proved time and time again when taking Databases that have been installed by 3rd party vendors that do not match my collation. The worst of which was a Dynamics GP 10 Database.

Paulus


It just means you are running SQL Server 2005 without service packs because what we are talking about requires SP2 and up. And since 2007 SP2 is required for most development projects.





Kind regards,
Gift Peddie
Post #822550
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse