SQL server collation
-
Hi, I have one problem with sql server collation. How can I change server collation without reinstall ? Database has czech collation 1250 case insensitive, and server has english collation. Is there any possibility to do so without reinstall ?
-
To change the collation you can either detach the databases, change the server collation, rebuild the master database etc., reattach each user database and run the script below (wrote it a long time ago!) to generate another script that will change the collation for the database its run on (in this case the collation is Latin1_General_CI_AS you'll need to change this to the one you require) - can't be 100% sure you still won't have issues though I've only ever had to do individual databases (its simpler to change collation for single db rather than whole server!). Tend to get this issue whenever customers upgrade and choose the default collation which is different between sql7 & 2k - dont know who made that decision!
theres a knowledge base article @ ms so I'd take a look at that 1st.
-----------------------------------------------------------------------
-- handle check constraints
-----------------------------------------------------------------------
--drop table
print'if exists(select * from sysobjects where name = ''TEMP_CHECKS'')
drop table TEMP_CHECKS
' + char(71) + char(79)
GO
--insert check constraints into table
print 'if not exists(select * from sysobjects where name = ''TEMP_CHECKS'')
begin
select identity (int,1,1) as id, tables.name as table_name, checks.name as check_name, cols.name as column_name, txt.text as check_text into TEMP_CHECKS
from
sysobjects checks join sysdepends deps on checks.id = deps.id
join sysobjects tables on deps.depid = tables.id
join syscolumns cols on cols.id = tables.id and cols.colorder = deps.depnumber
join syscomments txt on txt.id = checks.id
where
checks.xtype = ''C'' and tables.xtype = ''U''
end
' + char(71) + char(79)
GO
--drop check constraints in database
print 'if exists(select * from sysobjects where name = ''TEMP_CHECKS'')
BEGIN
--drop constraints
declare @id int
declare @STR varchar(8000)
set @id = 0
while @id < (select count(*) from TEMP_CHECKS)
begin
set @id = @id + 1
select @STR = ''if exists(select * from sysobjects o where o.name = ''''''' + ' + check_name + ' + ''''''' and xtype = ''''C'''')'' + char(10) +
''ALTER TABLE '' + table_name + '' DROP CONSTRAINT '' + check_name from TEMP_CHECKS WHERE id = @id
exec (@str)
end
END
' + char(71) + char(79)
GO
----------------------------------------------------------------------------
-- unbind defaults bound to text/ntext columns
--drop table
print'if exists(select * from sysobjects where name = ''TEMP_DEFAULTS'')
drop table TEMP_DEFAULTS
' + char(71) + char(79)
GO
print 'if not exists(select * from sysobjects where name = ''TEMP_DEFAULTS'')
select identity(int,1,1) as id, c.name as column_name ,o.name as table_name, defs.name as default_name into TEMP_DEFAULTS
from syscolumns c
join sysobjects o on o.id = c.id
join sysobjects defs on defs.id = c.cdefault
join syscomments txt on txt.id = defs.id
join systypes typ on typ.xtype= c.xtype
where cdefault <> 0 and defs.name like ''%empty%string%''
and defs.parent_obj = 0 and defs.type = ''d'' and typ.xtype in (35,99)
' + char(71) + char(79)
GO
--unbind defaults so column may be dropped
print 'if exists(select * from sysobjects where name = ''TEMP_DEFAULTS'')
BEGIN
--drop constraints
declare @id int
declare @STR varchar(8000)
set @id = 0
while @id < (select count(*) from TEMP_DEFAULTS)
begin
set @id = @id + 1
select @STR = ''if exists(select * from sysobjects o where o.name = ''''''' + ' + default_name + ' + ''''''' and xtype = ''''D'''')'' + char(10) +
''exec sp_unbindefault '''''' + table_name + ''.'' + column_name + '''''''' from TEMP_DEFAULTS WHERE id = @id
exec (@str)
end
END
' + char(71) + char(79)
GO
BEGIN
set nocount on
print 'alter database ' + db_name() + ' collate Latin1_General_CI_AS' + char(10) + 'go'
--cursor for tables
DECLARE @id int, @tablename varchar(128)
DECLARE tablecursor CURSOR FOR
SELECT o.id ,o.name
FROM sysobjects o join syscolumns c on o.id = c.id
WHERE c.xtype in (175,239,99,231,35,167) and o.type = 'U' GROUP BY o.name,o.id
order by o.name
--cursor for columns
DECLARE @coltype int, @colname varchar(128),@colid int,@collen int,@isnullable bit
DECLARE @oldcolname varchar(128)
--cursor for indexes
DECLARE @indid int, @indexname varchar(128),@keyno int, @tableid int, @status int, @allownulls int
if exists(select * from tempdb.dbo.sysobjects where name like '#table%')-- and uid = (select uid from sysusers where name = system_user))
drop table #table
if exists(select * from tempdb.dbo.sysobjects where name like '#index%')-- and uid = (select uid from sysusers where name = system_user))
drop table #index
if exists(select * from tempdb.dbo.sysobjects where name like '#indexcols%')-- and uid = (select uid from sysusers where name = system_user))
drop table #indexcols
--create tables for indexe
CREATE TABLE #table (
tableid int NOT NULL,
tablename varchar(128) NOT NULL)
CREATE TABLE #index (
indexid int NOT NULL,
tableid int NOT NULL,
indexname varchar(128) NOT NULL,
status int NOT NULL
)
CREATE TABLE #indexcols (
tableid int,
indexid int,
colorder int,
colid int,
colname varchar(128),
allownulls bit)
OPEN tablecursor
-- Perform the first fetch.
FETCH NEXT FROM tablecursor into @id, @tablename
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--first get indexes
print '--' + @TABLENAME
--select * from syscolumns
DECLARE colindex CURSOR FOR
select i.name ,c.name, k.indid, k.keyno, k.colid, c.name, i.status, c.isnullable
from sysindexes i join sysobjects o on o.id = i.id
join sysindexkeys k on k.id = i.id and k.indid = i.indid
join syscolumns c on o.id = c.id and c.colid = k.colid
where
o.id = @id and
--only indexes with text/varchar etc.
exists(
select *
from syscolumns c2 join sysindexkeys k2 on c2.id = k2.id and k2.colid = c2.colid
where k2.id = k.id and k2.indid = k.indid and c2.xtype in (175,239,99,231,35,167)
 
set nocount on
--store indexes
open colindex
FETCH NEXT FROM colindex into @indexname, @colname, @indid, @keyno,@colid,@colname,@status,@allownulls
WHILE @@FETCH_STATUS = 0
BEGIN
--statistics check
if (@status & (64 | 8388608)) > 0
BEGIN
--statistics if same name as column??
PRINT 'DROP STATISTICS [' + @tablename + '].[' + @indexname + ']'
END
else --index
BEGIN
--drop index and store details
if not exists(select * from #table where tableid = @id)
BEGIN
insert #table (tableid,tablename) values (@id,@tablename)
END
if not exists(select * from #index where tableid = @id and indexid = @indid)
BEGIN
if (@status & 2048) > 0
PRINT 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @indexname + char(10) + 'GO'
else
if (@status & (4096)) > 0 and (@status & (4096)) > 0
PRINT 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @indexname + '' + char(10) + 'GO'
else
PRINT 'DROP INDEX ' + @tablename + '.' + @indexname + '' + char(10) + 'GO'
--print char(9) + 'CREATE INDEX ' + @indexname + 'ON' + @tablename + ' ('
insert #index (indexid,tableid,indexname,status) values (@indid,@id,@indexname,@status)
END
insert #indexcols (tableid,indexid,colorder,colid,colname,allownulls) values (@id,@indid,@keyno,@colid,@colname,@allownulls)
END
FETCH NEXT FROM colindex into @indexname, @colname, @indid, @keyno,@colid,@colname, @status, @allownulls
END
close colindex
deallocate colindex
DECLARE colcursor CURSOR FOR
SELECT c.name,c.xtype,c.colid ,c.length,c.isnullable
FROM sysobjects o join syscolumns c on o.id = c.id
WHERE c.xtype in (175,239,99,231,35,167) and o.type = 'U' and o.id = @id
--now loop through
OPEN colcursor
FETCH NEXT FROM colcursor into @colname, @coltype, @colid, @collen,@isnullable
WHILE @@FETCH_STATUS = 0
BEGIN
print '--' + @colname
--select * from systypes where xtype in (175,239,99,231,35,167)
if @coltype = 175 --char
print 'ALTER TABLE ' + @tablename + ' ALTER COLUMN [' + @colname + '] char(' + cast(@collen as varchar(10)) + ') COLLATE Latin1_General_CI_AS ' + case when @isnullable = 1 then ' NULL' else ' NOT NULL' end + CHAR(10) + 'GO' + CHAR(10)
else
if @coltype = 239 --nchar
print 'ALTER TABLE ' + @tablename + ' ALTER COLUMN [' + @colname + '] nchar(' + cast(@collen/2 as varchar(10)) + ') COLLATE Latin1_General_CI_AS ' + case when @isnullable = 1 then ' NULL' else ' NOT NULL' end + CHAR(10) + 'GO' + CHAR(10)
else
if @coltype = 99 --ntext
BEGIN
--add nullable column
print 'ALTER TABLE ' + @tablename + ' ADD [' + @colname + '_TEMP] ntext COLLATE Latin1_General_CI_AS NULL' + CHAR(10) + 'GO' + CHAR(10)
--update to be same as col replacing
print 'UPDATE ' + @tablename + ' SET [' + @colname + '_TEMP] = [' + @colname + ']' + CHAR(10) + 'GO' + CHAR(10)
-- change to not null when data in
if @isnullable = 0
print 'ALTER TABLE ' + @tablename + ' ALTER COLUMN [' + @colname + '] ntext COLLATE Latin1_General_CI_AS NOT NULL' + CHAR(10) + 'GO' + CHAR(10)
--drop original column
print 'ALTER TABLE ' + @tablename + ' DROP COLUMN [' + @colname + ']' + CHAR(10) + 'GO' + CHAR(10)
--rename to be same as original
print 'exec sp_rename ''' + @tablename + '.' + @colname + '_TEMP'',''' + @colname + '''' + char(10) + 'GO' + CHAR(10)
END
else
if @coltype = 35 --text
BEGIN
--add nullable column
print 'ALTER TABLE ' + @tablename + ' ADD [' + @colname + '_TEMP] text COLLATE Latin1_General_CI_AS NULL' + CHAR(10) + 'GO' + CHAR(10)
--update to be same as col replacing
print 'UPDATE ' + @tablename + ' SET [' + @colname + '_TEMP] = [' + @colname + ']' + CHAR(10) + 'GO' + CHAR(10)
-- change to not null when data in
if @isnullable = 0
print 'ALTER TABLE ' + @tablename + ' ALTER COLUMN [' + @colname + '] ntext COLLATE Latin1_General_CI_AS NOT NULL' + CHAR(10) + 'GO' + CHAR(10)
--drop original column
print 'ALTER TABLE ' + @tablename + ' DROP COLUMN [' + @colname + ']' + CHAR(10) + 'GO' + CHAR(10)
--rename to be same as original
print 'exec sp_rename ''' + @tablename + '.' + @colname + '_TEMP'',''' + @colname + '''' + char(10) + 'GO' + CHAR(10)
END
else
if @coltype = 167 --varchar
print 'ALTER TABLE ' + @tablename + ' ALTER COLUMN [' +
@colname + '] varchar(' + cast(@collen as varchar(10)) + ') COLLATE Latin1_General_CI_AS' + case when @isnullable = 1 then ' NULL' else ' NOT NULL' end + CHAR(10) + 'GO' + CHAR(10)
else
if @coltype = 231 --nvarchar
print 'ALTER TABLE ' + @tablename + ' ALTER COLUMN [' +
@colname + '] nvarchar(' + cast(@collen/2 as varchar(10)) + ') COLLATE Latin1_General_CI_AS' + case when @isnullable = 1 then ' NULL' else ' NOT NULL' end + CHAR(10) + 'GO' + CHAR(10)
--A computed column. - Not applicable 2k only
--Distribution statistics - need to delete for all columns
--A CHECK constraint. - not applicable
--A FOREIGN KEY constraint. - shouldn't be any on varchar/text/char fields!
FETCH NEXT FROM colcursor into @colname, @coltype, @colid, @collen,@isnullable
END
close colcursor
DEALLOCATE colcursor
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM tablecursor into @id,@tablename
END
CLOSE tablecursor
DEALLOCATE tablecursor
DECLARE indexcursor CURSOR for
select t.tableid,t.tablename,i.indexid,i.indexname, i.status from #index i join #table t on t.tableid = i.tableid
OPEN indexcursor
FETCH NEXT FROM indexcursor into @tableid, @tablename, @indid, @indexname, @status
WHILE @@FETCH_STATUS = 0
BEGIN
--primary key
if (@status & 2048) > 0
print 'alter table ' + @tablename + ' add constraint ' + @indexname + ' PRIMARY KEY ('
else
--index
if (@status & 4096) > 0
--unique constraint
print 'alter table ' + @tablename + ' add constraint ' + @indexname + ' UNIQUE ('
else
--index
print 'CREATE' + case when @status & 2>0 then ' UNIQUE ' else '' END + ' NONCLUSTERED INDEX ' + @indexname + ' ON ' + @tablename + '('
DECLARE colindex CURSOR for
select colname from #indexcols where tableid = @tableid and indexid = @indid order by colorder asc
open colindex
FETCH NEXT FROM colindex into @colname
WHILE @@FETCH_STATUS = 0
BEGIN
set @oldcolname = @colname
FETCH NEXT FROM colindex into @colname
IF @@FETCH_STATUS = 0
PRINT '[' + @oldcolname + '],'
ELSE
PRINT '[' + @oldcolname +']'
END
print ')'
print 'GO'
close colindex
deallocate colindex
FETCH NEXT FROM indexcursor into @tableid, @tablename, @indid, @indexname, @status
END
close indexcursor
DEALLOCATE indexcursor
set nocount off
END
GO
--recreate check constraints
print 'if exists(select * from sysobjects where name = ''TEMP_CHECKS'')
BEGIN
--drop constraints
declare @id int
declare @STR varchar(8000)
set @id = 0
while @id < (select count(*) from TEMP_CHECKS)
begin
set @id = @id + 1
select @STR = ''if not exists(select * from sysobjects o where o.name = ''''''' + ' + check_name + ' + ''''''' and xtype = ''''C'''')'' + char(10) +
''ALTER TABLE '' + table_name + '' ADD CONSTRAINT '' + check_name + '' check '' + check_text from TEMP_CHECKS WHERE id = @id
exec (@str)
end
END
' + char(71) + char(79)
GO
--rebind defaults
print 'if exists(select * from sysobjects where name = ''TEMP_DEFAULTS'')
BEGIN
--drop constraints
declare @id int
declare @STR varchar(8000)
set @id = 0
while @id < (select count(*) from TEMP_DEFAULTS)
begin
set @id = @id + 1
select @STR = ''if exists(select * from sysobjects o where o.name = ''''''' + ' + default_name + ' + ''''''' and xtype = ''''D'''')'' + char(10) +
''exec sp_bindefault '''''' + default_name + '''''' , '''''' + table_name + ''.'' + column_name + '''''''' from TEMP_DEFAULTS WHERE id = @id
exec (@str)
end
END
' + char(71) + char(79)
GO
Phil Nicholas
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply