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

General SQL Server Forums

Script Library

Changing Database collation (sql server 2005/2008)

Author

Topic

Kautik
Starting Member

5 Posts

Posted - 2010-09-08 : 04:14:06

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

--verify that collation change was successfull

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-08 : 08:52:20

Would you mind adding [ code ] and [/ code ] tags around you procedure so that the formatting is preserved...?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources