Changing Database collation (sql server 2005/2008)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Changing Database collation (sql server 2005/2008)

Author

Topic

Kautik
Starting Member

5 Posts

Posted - 2010-09-07 : 02:54:20

Just changing the collation of database doesnt mean we change the collation of its tables and columns of tables. Change collation query needs to be executed on each of the table and its column individually.
In order to change the collation of database we need to drop following:
1. All check constraints
2. All Foreign Key Constraints
3. All the indexes
4. All Unique Key constraints
5. All views of the database.
After dropping above things we can execute query using " Collate " keyword to change collation of database, tables and all columns.
Once collation is changed we can re-apply all the above dropped things.

e.g. EXEC('ALTER DATABASE [' + @dbname + '] COLLATE '+ @srvrcollation)
Note: We dont need to drop and re-create primary keys.

Please let me know if any one of us have used this approach in production.

I have written following script to change the Db collation to that of sql server default

---Script to change the collation of the database

--- Fill data to be used during verification at the end
DECLARE @srvrcollation NVARCHAR(255)
SELECT @srvrcollation = CONVERT(NVARCHAR(255), SERVERPROPERTY( N'COLLATION'))

DECLARE @CheckScriptSuccess table (viewsCount int)

INSERT INTO @CheckScriptSuccess (viewsCount)
values (
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS)
)

--Check whether Database collation is different than Sql Server Collation
DECLARE @dbcollation NVARCHAR(255)
DECLARE @dbname NVARCHAR(255)

SET @dbname=db_name()
SELECT @dbcollation = convert(nVARCHAR(255), databasepropertyex(@dbname, N'COLLATION'))
PRINT 'Collation Of Sql Server is :'+ @srvrcollation;
PRINT 'Collation Of Database is :' + @dbcollation;

IF @srvrcollation <> @dbcollation
BEGIN
PRINT 'Preparing to change the collation of database'
--1. drop all Views of the database
PRINT 'Droping existing views of database'
DECLARE
@object_name NVARCHAR(255),
@object_schemaName NVARCHAR(255),
@object_view VARCHAR(2000),
@sql NVARCHAR (1000);
DECLARE @viewDefinition TABLE (query VARCHAR(2000))

DECLARE SPCursor CURSOR FOR
SELECT table_Name,view_definition,table_Schema FROM INFORMATION_SCHEMA.VIEWS order by table_Name
OPEN SPCursor;
FETCH NEXT FROM SPCursor INTO @object_name,@object_view,@object_schemaName;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @viewDefinition (query) VALUES (@object_view)
SET @sql = N'IF (OBJECT_ID(''['+@object_schemaName+'].['+ @object_name+']'') IS NOT NULL) ' + CHAR(13)+ CHAR(10) +'DROP VIEW ['+@object_schemaName+'].['+ @object_name+']';
EXEC sp_executesql @sql;
--PRINT @sql
FETCH NEXT FROM SPCursor INTO @object_name,@object_view,@object_schemaName;
END
CLOSE SPCursor
DEALLOCATE SPCursor
PRINT 'All Views of database dropped';

-- 2 Fill All checks contraints details
declare @checkConstraintDetails table (constName varchar(255),checkClause varchar(2000),
tableName varchar(255),tableSchema varchar(255))

insert into @checkConstraintDetails (constName,checkClause,tableName,tableSchema)
select c.CONSTRAINT_NAME,c.CHECK_CLAUSE,t.table_name,t.TABLE_SCHEMA from INFORMATION_SCHEMA.CHECK_CONSTRAINTS c join
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE t on c.CONSTRAINT_NAME = t.CONSTRAINT_NAME

DECLARE @const_name nVARCHAR(255),
@check_Clause nvarchar(2000),
@Constraint_table_Name nvarchar(255),
@Constraint_tableSchema nvarchar(255),
@sqlConstraint nvarchar(4000)

--- 3. Drop all Check Constraints
DECLARE DropChecksCursor CURSOR FOR
SELECT constName,tableName,tableSchema FROM @checkConstraintDetails

OPEN DropChecksCursor;
FETCH NEXT FROM DropChecksCursor INTO @const_name,@Constraint_table_Name,@Constraint_tableSchema;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlConstraint = 'ALTER TABLE ['+ @Constraint_tableSchema + '].['+ @Constraint_table_Name+']
DROP CONSTRAINT '+ @const_name + ';'
EXEC sp_executesql @sqlConstraint;
PRINT @sqlConstraint
FETCH NEXT FROM DropChecksCursor INTO @const_name,@Constraint_table_Name,@Constraint_tableSchema;
END
CLOSE DropChecksCursor
DEALLOCATE DropChecksCursor
PRINT 'all checked constraint Droped';

-- 4. Get queries to create and drop PK, FK, UK and indexes
DECLARE @createQuery TABLE (query VARCHAR(2000))
DECLARE @dropquery TABLE (query VARCHAR(2000))

---fill queries to drop and create UK and Indexes
DECLARE @Store_TableName VARCHAR(128)
DECLARE @Index_Name VARCHAR(128)
DECLARE @IndexId int
DECLARE @IndexKey int

DECLARE Table_Cursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables WHERE table_type != 'VIEW'

OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor
INTO @Store_TableName

--loop through tables
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT @Store_TableName

DECLARE Index_Cursor CURSOR FOR
SELECT indid, name FROM sysindexes
WHERE id = OBJECT_ID(@Store_TableName) and indid > 0 and indid < 255 and (status & 64)=0 and
not exists(SELECT top 1 NULL FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = @Store_TableName AND (CONSTRAINT_TYPE = 'PRIMARY KEY' or CONSTRAINT_TYPE = 'UNIQUE') and
CONSTRAINT_NAME = name)
order by indid

OPEN Index_Cursor
FETCH NEXT FROM Index_Cursor
INTO @IndexId, @Index_Name

--loop through indexes
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @SQL_String VARCHAR(256)
SET @SQL_String = 'drop index '
SET @SQL_String = @SQL_String + @Store_TableName + '.' + @Index_Name

INSERT INTO @dropquery (query) values (@SQL_String)

SET @SQL_String =' '
SET @SQL_String = @SQL_String + '; create '

if( (SELECT INDEXPROPERTY ( OBJECT_ID(@Store_TableName) , @Index_Name , 'IsUnique')) =1)
SET @SQL_String = @SQL_String + 'unique '

if( (SELECT INDEXPROPERTY ( OBJECT_ID(@Store_TableName) , @Index_Name , 'IsClustered')) =1)
SET @SQL_String = @SQL_String + 'clustered '

SET @SQL_String = @SQL_String + 'index '
SET @SQL_String = @SQL_String + @Index_Name
SET @SQL_String = @SQL_String + ' on '
SET @SQL_String = @SQL_String + @Store_TableName
SET @SQL_String = @SQL_String + '('

--form column list
SET @IndexKey = 1

-- Loop through index columns, INDEX_COL can be FROM 1 to 16.
WHILE @IndexKey <= 16 and INDEX_COL(@Store_TableName, @IndexId, @IndexKey)
IS NOT NULL
BEGIN
IF @IndexKey != 1
SET @SQL_String = @SQL_String + ','
SET @SQL_String = @SQL_String + index_col(@Store_TableName, @IndexId, @IndexKey)
SET @IndexKey = @IndexKey + 1
END

SET @SQL_String = @SQL_String + ')'
INSERT INTO @createQuery (query) values (@SQL_String)

FETCH NEXT FROM Index_Cursor
INTO @IndexId, @Index_Name
END

CLOSE Index_Cursor
DEALLOCATE Index_Cursor

--loop through unique constraints
DECLARE Contraint_Cursor CURSOR FOR
SELECT indid, name FROM sysindexes
WHERE id = OBJECT_ID(@Store_TableName) and indid > 0 and indid < 255 and (status & 64)=0 and
exists( SELECT top 1 NULL FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = @Store_TableName AND CONSTRAINT_TYPE = 'UNIQUE' and CONSTRAINT_NAME = name)
order by indid

OPEN Contraint_Cursor
FETCH NEXT FROM Contraint_Cursor
INTO @IndexId, @Index_Name

--loop through indexes
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL_String = 'alter table '
SET @SQL_String = @SQL_String + @Store_TableName
SET @SQL_String = @SQL_String + ' drop constraint '
SET @SQL_String = @SQL_String + @Index_Name

INSERT INTO @dropquery (query) values (@SQL_String)

SET @SQL_String = ' '
SET @SQL_String = @SQL_String + '; alter table '
SET @SQL_String = @SQL_String + @Store_TableName
SET @SQL_String = @SQL_String + ' WITH NOCHECK add constraint '
SET @SQL_String = @SQL_String + @Index_Name
SET @SQL_String = @SQL_String + ' unique '

if( (SELECT INDEXPROPERTY ( OBJECT_ID(@Store_TableName) , @Index_Name , 'IsClustered')) =1)
SET @SQL_String = @SQL_String + 'clustered '

SET @SQL_String = @SQL_String + '('

--form column list
SET @IndexKey = 1

-- Loop through index columns, INDEX_COL can be FROM 1 to 16.
WHILE @IndexKey <= 16 and INDEX_COL(@Store_TableName, @IndexId, @IndexKey)
IS NOT NULL
BEGIN
IF @IndexKey != 1
SET @SQL_String = @SQL_String + ','
SET @SQL_String = @SQL_String + index_col(@Store_TableName, @IndexId, @IndexKey)
SET @IndexKey = @IndexKey + 1
END

SET @SQL_String = @SQL_String + ')'
INSERT INTO @createQuery (query) values (@SQL_String)

FETCH NEXT FROM Contraint_Cursor
INTO @IndexId, @Index_Name
END

CLOSE Contraint_Cursor
DEALLOCATE Contraint_Cursor

FETCH NEXT FROM Table_Cursor
INTO @Store_TableName
END

CLOSE Table_Cursor
DEALLOCATE Table_Cursor

PRINT ''
PRINT 'Finished, Please check output for errors.'

---- Fill FK queries
SET NOCOUNT ON

DECLARE @operation VARCHAR(10)
DECLARE @tableName sysname
DECLARE @schemaName sysname

DECLARE cursor_tablekeys CURSOR FOR
SELECT t.table_name,t.table_schema FROM Information_schema.tables t
OPEN cursor_tablekeys

FETCH NEXT FROM cursor_tablekeys
INTO @tableName,@schemaName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @operation = 'DROP'

DECLARE @cmd NVARCHAR(1000)

DECLARE
@FK_NAME sysname,
@FK_OBJECTID INT,
@FK_DISABLED INT,
@FK_NOT_FOR_REPLICATION INT,
@DELETE_RULE smallint,
@UPDATE_RULE smallint,
@FKTABLE_NAME sysname,
@FKTABLE_OWNER sysname,
@PKTABLE_NAME sysname,
@PKTABLE_OWNER sysname,
@FKCOLUMN_NAME sysname,
@PKCOLUMN_NAME sysname,
@CONSTRAINT_COLID INT

DECLARE cursor_fkeys CURSOR FOR
SELECT Fk.name,
Fk.OBJECT_ID,
Fk.is_disabled,
Fk.is_not_for_replication,
Fk.delete_referential_action,
Fk.update_referential_action,
OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,
schema_name(Fk.schema_id) AS Fk_table_schema,
TbR.name AS Pk_table_name,
schema_name(TbR.schema_id) Pk_table_schema
FROM sys.foreign_keys Fk LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join
WHERE TbR.name = @tableName
AND schema_name(TbR.schema_id) = @schemaName

OPEN cursor_fkeys

FETCH NEXT FROM cursor_fkeys
INTO @FK_NAME,@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER

WHILE @@FETCH_STATUS = 0
BEGIN
-- create statement for dropping FK and also for recreating FK
IF @operation = 'DROP'
BEGIN

-- drop statement
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] DROP CONSTRAINT [' + @FK_NAME + ']'

INSERT INTO @dropquery ( query) values (@cmd)

-- create process
DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT

-- create cursor to get FK columns
DECLARE cursor_fkeyCols CURSOR FOR
SELECT COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,
COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name
FROM sys.foreign_keys Fk LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN
sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID
WHERE TbR.name = @tableName
AND schema_name(TbR.schema_id) = @schemaName
AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008
ORDER BY Fk_Cl.constraint_column_id

OPEN cursor_fkeyCols

FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME

SET @COUNTER = 1
SET @FKCOLUMNS = ''
SET @PKCOLUMNS = ''

WHILE @@FETCH_STATUS = 0
BEGIN
IF @COUNTER > 1
BEGIN
SET @FKCOLUMNS = @FKCOLUMNS + ','
SET @PKCOLUMNS = @PKCOLUMNS + ','
END

SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'
SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'

SET @COUNTER = @COUNTER + 1
FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
END

CLOSE cursor_fkeyCols
DEALLOCATE cursor_fkeyCols

-- generate create FK statement
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] WITH ' +
CASE @FK_DISABLED
WHEN 0 THEN ' CHECK '
WHEN 1 THEN ' NOCHECK '
END + ' ADD CONSTRAINT [' + @FK_NAME
+ '] FOREIGN KEY (' + @FKCOLUMNS
+ ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('
+ @PKCOLUMNS + ') ON UPDATE ' +
CASE @UPDATE_RULE
WHEN 0 THEN ' NO ACTION '
WHEN 1 THEN ' CASCADE '
WHEN 2 THEN ' SET_NULL '
END + ' ON DELETE ' +
CASE @DELETE_RULE
WHEN 0 THEN ' NO ACTION '
WHEN 1 THEN ' CASCADE '
WHEN 2 THEN ' SET_NULL '
END + '' +
CASE @FK_NOT_FOR_REPLICATION
WHEN 0 THEN ''
WHEN 1 THEN ' NOT FOR REPLICATION '
END
INSERT INTO @createQuery (query) values (@cmd)
END

FETCH NEXT FROM cursor_fkeys
INTO @FK_NAME,@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER
END

CLOSE cursor_fkeys
DEALLOCATE cursor_fkeys

FETCH NEXT FROM cursor_tablekeys
INTO @tableName,@schemaName
END

CLOSE cursor_tablekeys
DEALLOCATE cursor_tablekeys
;

---5. Droping all the constraints
DECLARE
@object_query nVARCHAR(255)

DECLARE queryCursor CURSOR FOR
SELECT query FROM @dropquery

OPEN queryCursor;
FETCH NEXT FROM queryCursor INTO @object_query;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @object_query = @object_query + ';'
EXEC sp_executesql @object_query;
PRINT @object_query
FETCH NEXT FROM queryCursor INTO @object_query;
END
CLOSE queryCursor
DEALLOCATE queryCursor
PRINT 'all constraint droped'
;

-- 6. Change Collation of database and it columns

EXEC('ALTER DATABASE [' + @dbname + '] COLLATE '+ @srvrcollation)
PRINT('ALTERING DATABASE [' + @dbname + '] TO COLLATION '+ @srvrcollation + ' FROM ' + @dbcollation)

-- 7. Changing the collation of columns

DECLARE COL_CURSOR CURSOR READ_ONLY FOR
SELECT table_schema, table_name, column_name,
column_default, is_Nullable, Data_type,
character_maximum_length, collation_name
FROM INFORMATION_SCHEMA.COLUMNS
INNER JOIN
(SELECT TABLE_NAME TN FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' ) IT ON (TABLE_NAME=TN)
WHERE (Data_type LIKE '%char%' OR Data_type LIKE '%text%')
AND collation_name <> @srvrcollation

DECLARE @table_schema VARCHAR(10), @table_name VARCHAR(100), @column_name VARCHAR(100),
@column_default VARCHAR(100), @is_Nullable VARCHAR(5), @Data_type VARCHAR(100),
@character_maximum_length VARCHAR(10), @columncollation VARCHAR(200)

DECLARE @Execstr VARCHAR(2000)
OPEN COL_CURSOR
FETCH NEXT FROM COL_CURSOR INTO @table_schema, @table_name, @column_name,
@column_default, @is_Nullable, @Data_type,
@character_maximum_length, @columncollation
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @Execstr = 'ALTER TABLE ' + @table_schema + '.' + @table_name
+ ' ALTER COLUMN [' + @column_name + '] ' + @Data_type + ' ('+ @character_maximum_length + ') '
+ CASE WHEN @is_Nullable='no' THEN ' NOT NULL' ELSE ' NULL ' END

EXEC (@Execstr)

PRINT ('Executing -->' + @Execstr )
PRINT ('--Orig COLLATION WAS -->' + @columncollation )
END
FETCH NEXT FROM COL_CURSOR INTO @table_schema, @table_name, @column_name,
@column_default, @is_Nullable, @Data_type,
@character_maximum_length, @columncollation
END
CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR
;

--- 8. ReCreating all views
DECLARE
@object_Viewquery nVARCHAR(2000)

DECLARE viewCursor CURSOR FOR
SELECT query FROM @viewDefinition
DECLARE @times int
declare @viewcount int

SELECT @viewcount=count(*) FROM INFORMATION_SCHEMA.VIEWS
--iterating since some views are created based on other views
SELECT @times=viewsCount from @CheckScriptSuccess ;
while @times != @viewcount
BEGIN
OPEN viewCursor;
print '----------------------------------------'
print '--------No. of Views Created------------'
print @viewcount;
print '----------------------------------------'
print '----------------------------------------'
FETCH NEXT FROM viewCursor INTO @object_Viewquery;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @object_Viewquery = @object_Viewquery + ';'

BEGIN TRY
EXEC sp_executesql @object_Viewquery;
END TRY
BEGIN CATCH
print 'in process of creating views'
END CATCH
PRINT @object_Viewquery
FETCH NEXT FROM viewCursor INTO @object_Viewquery;
END
CLOSE viewCursor
SELECT @viewcount=count(*) FROM INFORMATION_SCHEMA.VIEWS
END
DEALLOCATE viewCursor
PRINT 'all views Created';

--- 9. ReCreating all the constraints
DECLARE
@object_requery nVARCHAR(2000)

DECLARE reQueryCursor CURSOR FOR
SELECT query FROM @createquery

OPEN reQueryCursor;
FETCH NEXT FROM reQueryCursor INTO @object_requery;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @object_requery = @object_requery + ';'
EXEC sp_executesql @object_requery;
PRINT @object_requery
FETCH NEXT FROM reQueryCursor INTO @object_requery;
END
CLOSE reQueryCursor
DEALLOCATE reQueryCursor
PRINT 'all constraint Created'
;

--- 10. Create all checks Constraints
DECLARE CreateChecksCursor CURSOR FOR
SELECT constName,checkClause,tableName,tableSchema FROM @checkConstraintDetails

OPEN CreateChecksCursor;
FETCH NEXT FROM CreateChecksCursor INTO @const_name,@check_Clause,@Constraint_table_Name,@Constraint_tableSchema;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlConstraint = 'ALTER TABLE ['+ @Constraint_tableSchema + '].['+ @Constraint_table_Name+']
ADD CONSTRAINT '+ @const_name + ' CHECK '+ @check_Clause +';'
EXEC sp_executesql @sqlConstraint;
PRINT @sqlConstraint
FETCH NEXT FROM CreateChecksCursor INTO @const_name,@check_Clause,@Constraint_table_Name,@Constraint_tableSchema;
END
CLOSE CreateChecksCursor
DEALLOCATE CreateChecksCursor
PRINT 'all checked constraint Created';

END

PRINT '---- Collation Change Done Successfully -------'

Sachin.Nand

2937 Posts

Posted - 2010-09-08 : 02:31:41

Do having any problems with the above code?
If not then please post it in the Script Library Forum.

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=11

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Kautik
Starting Member

5 Posts

Posted - 2010-09-08 : 02:33:38

the code works fine...

Sachin.Nand

2937 Posts

Posted - 2010-09-08 : 02:37:36

quote:
Originally posted by Kautik

the code works fine...

That is good.

Then if want to share it with every one then it is recommended you post it in Script Library in the link below.

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=11

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources