Script to Change Collation of User Database

  • lokhande.deepali

    SSC Eights!

    Points: 833

    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

  • Gift Peddie

    SSC Guru

    Points: 73570

    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

  • lokhande.deepali

    SSC Eights!

    Points: 833

    --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.

  • Gift Peddie

    SSC Guru

    Points: 73570

    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

  • Paul Smith-221741

    SSCommitted

    Points: 1953

    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

  • lokhande.deepali

    SSC Eights!

    Points: 833

    Hi Paul,

    Thanks..

    I will try this script.

    Rd,

    Deepali

  • Gift Peddie

    SSC Guru

    Points: 73570

    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

  • Subhash-63067

    SSCarpal Tunnel

    Points: 4171

    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

  • Paul Smith-221741

    SSCommitted

    Points: 1953

    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

  • Gift Peddie

    SSC Guru

    Points: 73570

    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

  • seraulu34

    SSC Rookie

    Points: 43

    :-D:-D:-D,

    thanks for that idea;-)

  • kevinrhino

    Valued Member

    Points: 69

    Hi any chance you have the updated scripts for SQL 2008 R2 for the change of collations?

  • dan 35932

    SSC Rookie

    Points: 35

    We're you successful changing the collation of a company DB using SQL2008R2 and Dynamics 10?

  • Solomon Rutzky

    SSCoach

    Points: 16259

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply