November 18, 2009 at 3:23 am
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
November 18, 2009 at 9:17 am
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
November 18, 2009 at 9:36 pm
--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.
November 18, 2009 at 10:05 pm
lokhande.deepali (11/18/2009)
--I am assuming you know the whole server require master rebuildYes , 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
November 20, 2009 at 2:28 am
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 @ObjectIDint
Declare @IndexIDint
Declare @TableNamenvarchar(50)
Declare @IndexNamenvarchar(50)
declare @IndexTypenvarchar(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 @ObjectIDint
Declare @IndexIDint
Declare @TableNamenvarchar(50)
Declare @IndexNamenvarchar(50)
declare @IndexTypenvarchar(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
November 20, 2009 at 4:19 am
Hi Paul,
Thanks..
I will try this script.
Rd,
Deepali
November 20, 2009 at 7:46 am
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
November 20, 2009 at 9:36 am
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
November 20, 2009 at 10:05 am
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
November 20, 2009 at 10:26 am
Paul Smith-221741 (11/20/2009)
Dear AllMaybe 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
March 9, 2010 at 12:01 am
:-D:-D:-D,
thanks for that idea;-)
November 7, 2013 at 12:44 am
Hi any chance you have the updated scripts for SQL 2008 R2 for the change of collations?
March 28, 2016 at 3:54 pm
We're you successful changing the collation of a company DB using SQL2008R2 and Dynamics 10?
September 28, 2018 at 9:57 am
For anyone interested in the ramifications and potential blockers of changing a Database's Collation, or the entire Instance's Collation, and both what the documented and undocumented methods are, please see the following post:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply