|
Thanks very much for this tool!
well done on sqlserver2000
|
|
|
|
|
I hada similar issue with SQl 2000. But it appeared that I had another script window open in the Query Analyser and connected to the DB I wanted change collation for. So double check in the Enterprise Manager that you don't have any other connections...
BTW, great tool.
|
|
|
|
|
Runnin_Blind***
Love this STUFF!
|
|
|
|
|
You, sir, are a genius. I was just about to start writing a plugin for a code generator I'm using to do just this when I came across your tool. You've saved me several hours of work, and for that I kiss you (err, a virtual kiss on the cheek, eh?).
______________
Marc Heiligers Not one shred of evidence supports the notion that life is serious
|
|
|
|
|
Thanks - flattery will get you a long way.
I have posted an update to the code project that addresses some of the issues mentioned in the posts below and adds some SQL 2005 support. It should be published in a few days
Alex
|
|
|
|
|
Server: Msg 170, Level 15, State 1, Line 78
Line 78: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 296
Line 296: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 304
Line 304: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 308
Line 308: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 384
Line 384: Incorrect syntax near 'COLLATE'.
Server: Msg 156, Level 15, State 1, Line 480
Incorrect syntax near the keyword 'else'.
Server: Msg 170, Level 15, State 1, Line 483
Line 483: Incorrect syntax near 'COLLATE'.
Server: Msg 170, Level 15, State 1, Line 486
Line 486: Incorrect syntax near 'COLLATE'.
Server: Msg 170, Level 15, State 1, Line 488
Line 488: Incorrect syntax near 'COLLATE'.
Server: Msg 170, Level 15, State 1, Line 545
Line 545: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 653
Line 653: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 655
Line 655: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 657
Line 657: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 658
Line 658: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 665
Line 665: Incorrect syntax near 'collate'.
|
|
|
|
|
Could you send a little background? Which version of SQL server are you using - could you send me a copy of the generated script?
Thanks
Alex
|
|
|
|
|
Hi there, thank you for the quick response. here's the background, It is a server migration from SQL 7 to SQL 2000. The database works fine apart from a report function hence the need to change the collation. People are using the database at the moment so I have created a test db from a backup and have attempted to create the script from that.
Thanks
USE [Prism Test]
declare @textptr binary(16)
declare @SQLSegment nvarchar(4000)
declare @C Cursor
set nocount on
create table #SQL (ID int primary key identity(1,1),SQL nText)
insert into #SQL (SQL) values ('alter database [Prism Test] set single_user')
insert into #SQL (SQL) values ('USE [Prism Test]')
insert into #SQL (SQL)
select 'Alter table ['+ u.name + '].['+o.name+'] DROP CONSTRAINT [' + object_name(constid) + ']'
from sysconstraints c
join sysobjects o
on c.id = o.id
join sysusers u
on u.uid = o.uid
where objectproperty(constid,'IsForeignKey')=1
and constid in (
select fk.constid
from sysforeignkeys fk
join syscolumns fc
on fc.colid = fk.fkey
and fc.id = fk.fkeyid
join syscolumns rc
on rc.colid = fk.rkey
and rc.id = fk.rkeyid
where fc.collationid is not null
or rc.collationid is not null
or 1=1 ) --parameter allows all constraints to be dropped
/*script out dropping of check constraints */
insert into #SQL (SQL)
select 'Alter table ['+ u.name + '].['+o.name+'] drop constraint ['+object_name(cs.constid)+']'
from sysconstraints cs
join sysobjects o
on cs.id = o.id
join sysusers u
on u.uid = o.uid
where objectproperty(cs.constid,'IsCheckCnst') = 1
/*drop calculated columns*/
insert into #SQL (SQL)
select 'ALTER TABLE ['+ u.name + '].['+o.name+'] drop column ['+c.name+']'
from syscolumns c
join sysobjects o
on c.id = o.id
join sysusers u
on u.uid = o.uid
where c.iscomputed=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsTable')=1
--drop Table Functions
insert into #SQL (SQL)
select 'DROP FUNCTION ['+ u.name + '].['+o.name+']'
from sysobjects o
join sysusers u
on o.uid = u.uid
where objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsTableFunction')=1
-- script drop of indexes - we will also populate a temp table that helps recreate the indexes later
create table #spindtab
(
-- owner sysname collate database_default NOT NULL,
objectname sysname collate database_default NOT NULL,
index_name sysname collate database_default NOT NULL,
stats int,
groupname sysname collate database_default NOT NULL,
index_keys nvarchar(3000) collate database_default NOT NULL, -- see @IX_keys above for length descr
OrigFillFactor tinyint,
IsAutoStatistic bit
)
--generate SQL to do indexes
declare @IX_indid smallint, -- the index id of an index
@IX_groupid smallint, -- the filegroup id of an index
@IX_indname sysname,
@IX_groupname sysname,
@IX_status int,
@IX_keys nvarchar(3000),
@IX_dbname sysname,
@IX_ObjID int,
@IX_ObjName sysname,
@IX_OrigFillFactor tinyint,
@IX_IsAutoStatistic bit
-- Check to see the the table exists and initialize @IX_objid.
-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
declare ms_crs_ind cursor local static for
select i.id, '['+u.name+'].['+o.name+']' as objectname, i.indid, i.groupid, i.name, i.status, i.OrigFillFactor, case when (i.status & 64) = 0 then 0 else isnull(INDEXPROPERTY(i.id,i.name,'IsAutoStatistics'),0) end as IsAutoStatistic from sysindexes i
join sysobjects o
on i.id = o.id
join sysusers u
on u.uid = o.uid
where /*id = @IX_objid and */i.indid > 0 and i.indid < 255
and objectproperty(i.id,'ISMSSHIPPED')=0 and objectproperty(i.id,'IsTableFunction')=0
order by object_name(i.id),i.indid
open ms_crs_ind
fetch ms_crs_ind into @IX_objid, @IX_ObjName,@IX_indid, @IX_groupid, @IX_indname, @IX_status, @IX_OrigFillFactor, @IX_IsAutoStatistic
-- Now check out each index, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
while @@fetch_status >= 0
begin
-- First we'll figure out what the keys are.
declare @IX_i int, @IX_thiskey nvarchar(133) -- 128+5
declare @rebuild_index bit
select @IX_keys = '[' + index_col(@IX_objname, @IX_indid, 1)+']', @IX_i = 2, @rebuild_index=1 --parameter from application can force all to be rebuilt
if (indexkey_property(@IX_objid, @IX_indid, 1, 'IsDescending') = 1)
set @IX_keys = @IX_keys + ' DESC'
if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, 1, 'columnid')) is not null
set @rebuild_index=1
set @IX_thiskey = '[' + index_col(@IX_objname, @IX_indid, @IX_i) + ']'
if ((@IX_thiskey is not null) and (indexkey_property(@IX_objid, @IX_indid, @IX_i, 'IsDescending') = 1))
set @IX_thiskey = @IX_thiskey + ' DESC'
if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, @IX_i, 'columnid')) is not null
set @rebuild_index=1
while (@IX_thiskey is not null )
begin
select @IX_keys = @IX_keys + ', ' + @IX_thiskey, @IX_i = @IX_i + 1
if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, @IX_i, 'columnid')) is not null
set @rebuild_index=1
set @IX_thiskey = '[' + index_col(@IX_objname, @IX_indid, @IX_i) + ']'
if ((@IX_thiskey is not null) and (indexkey_property(@IX_objid, @IX_indid, @IX_i, 'IsDescending') = 1))
select @IX_thiskey = @IX_thiskey + ' DESC'
end
select @IX_groupname = groupname from sysfilegroups where groupid = @IX_groupid
-- INSERT ROW FOR INDEX
if @rebuild_index =1
insert into #spindtab values ( @IX_ObjName,@IX_indname, @IX_status, @IX_groupname, @IX_keys, @IX_OrigFillFactor, @IX_IsAutoStatistic)
-- Next index
fetch ms_crs_ind into @IX_objid, @IX_ObjName, @IX_indid, @IX_groupid, @IX_indname, @IX_status, @IX_OrigFillFactor, @IX_IsAutoStatistic
end
deallocate ms_crs_ind
-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
declare @IX_empty varchar(1) select @IX_empty = ''
declare @IX_des1 varchar(35), -- 35 matches spt_values
@IX_des2 varchar(35),
@IX_des4 varchar(35),
@IX_des32 varchar(35),
@IX_des64 varchar(35),
@IX_des2048 varchar(35),
@IX_des4096 varchar(35),
@IX_des8388608 varchar(35),
@IX_des16777216 varchar(35)
select @IX_des1 = name from master.dbo.spt_values where type = 'I' and number = 1 --ignoor duplicate keys
select @IX_des2 = name from master.dbo.spt_values where type = 'I' and number = 2 --unique
select @IX_des4 = name from master.dbo.spt_values where type = 'I' and number = 4 --ignoor duplicate rows
select @IX_des32 = name from master.dbo.spt_values where type = 'I' and number = 32 --hypothetical
select @IX_des64 = name from master.dbo.spt_values where type = 'I' and number = 64 --statistics
select @IX_des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048 --primary key
select @IX_des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096 --unique key
select @IX_des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608 --auto create
select @IX_des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216 --stats no recompute
insert into #SQL
select case when (stats & 4096)<>0 or (stats & 2048) <> 0 then
--Constraint
'ALTER TABLE '+objectname+' DROP CONSTRAINT ['+index_name+'] '
when (stats & 64) <> 0 then
'DROP STATISTICS '+objectname+'.['+index_name+'] '
else
-- index
'DROP INDEX '+objectname+'.['+ index_name +'] '
end
from #spindtab
-- script the changing of the database collation
insert into #SQL (SQL) values ('USE [MASTER]')
insert into #SQL (SQL) values ('alter database [Prism Test] collate Latin1_General_CI_AS')
insert into #SQL (SQL) values ('USE [Prism Test]')
-- Script out the creation of the table functions now as they may be used in triggers that will execute when columns are updated!
--first switch to use the correct database
insert into #SQL (SQL) values ('')
--get a text pointer
SELECT @textptr = TEXTPTR(SQL) FROM #SQL where ID = (select max(ID) from #SQL)
set @SQLSegment = '
USE [Prism Test]
'
UPDATETEXT #SQL.SQL @textptr NULL 0 @SQLSegment
--recreate functions - make sure owner name is specified!
declare @fn_name sysname,
@fn_id int,
@fn_idlast int,
@fn_last sysname,
@fn_owner sysname,
@fn_testText nvarchar(4000),
@fn_posCreateFn int,
@fn_posCommentStart int,
@fn_posCommentEnd int,
@fn_offset int,
@fn_posFunctionName int,
@fn_posObjectName int
set @fn_idlast =0
set @C = cursor for
select o.name as functionName,
o.id,
sc.text,
u.name
from sysobjects o
join syscomments sc
on o.id = sc.id
join sysusers u
on u.uid = o.uid
where objectproperty(o.id,'IsTableFunction') =1
order by o.id,
sc.colid
open @C
fetch next from @C into @fn_name, @fn_id, @SQLSegment, @fn_owner
while @@Fetch_Status=0
begin
if @fn_id<>@fn_idLast
begin
--add a row for our data
insert into #SQL (SQL) values ('')
--get a text pointer
SELECT @textptr = TEXTPTR(SQL) FROM #SQL where ID = (select max(ID) from #SQL)
set @fn_idLast =@fn_id
-- now look at the create function part of the SQL, make sure that the owner
-- name is specified
set @fn_posCreateFn = patindex('%create%function%', @SQLSegment)
set @fn_posCommentStart = patindex('%/*%', @SQLSegment)
set @fn_offset = 1
set @fn_testText = @SQLSegment
-- it is possible that there are create function statments in comments at the
-- start of the SQL so look for the one that actually creates the function
while @fn_posCommentStart <@fn_posCreateFn and @fn_posCommentStart<>0
begin
set @fn_posCommentEnd = patindex('%*/%', @fn_testText)
set @fn_offset = @fn_offset+@fn_posCommentEnd+1
set @fn_testText = substring(@SQLSegment, @fn_offset, len(@SQLSegment) - @fn_offset)
set @fn_posCreateFn = patindex('%create%function%', @fn_testText collate latin1_general_ci_ai)
set @fn_posCommentStart = patindex('%/*%', @fn_testText)
end
-- now look to see if the owner name is specified, there should be a
-- . before the function name so inspect the text between the word function and the actual
-- function name
set @fn_posFunctionName = charindex('function' collate latin1_general_ci_ai, @SQLSegment collate latin1_general_ci_ai, @fn_posCreateFn-1+@fn_offset)
if @fn_posFunctionName>0
set @fn_posFunctionName=@fn_posFunctionName+8 --number of characters in the word function
set @fn_posObjectName = charindex(@fn_name collate latin1_general_ci_ai, @SQLSegment collate latin1_general_ci_ai, @fn_posFunctionName)
set @fn_testText= substring(@SQLSegment,@fn_posFunctionName,@fn_posObjectName-@fn_posFunctionName)
if charindex('.',@fn_testText,1)=0
begin
-- the owner name is missing, add it
set @SQLSegment = left(@SQLSegment, @fn_posFunctionName) + '['+@fn_owner+'].'+substring(@SQLSegment, @fn_posObjectName,4000)
end
end
UPDATETEXT #SQL.SQL @textptr NULL 0 @SQLSegment
fetch next from @C into @fn_name, @fn_id, @SQLSegment, @fn_owner
end
Close @C
deallocate @C
-- now the permissions on the functions that have been recreated
insert into #SQL (SQL)
select case p.protecttype
when 206 then 'DENY '
else 'GRANT ' end +
case p.action
when 193 then 'SELECT'
when 26 then 'REFERENCES'
end+
' on ['+user_name(o.uid)+'].['+o.name+'] to ['+user_name(p.uid)+']' +
case when p.protecttype = 204 then ' WITH GRANT OPTION' else '' end
from sysprotects p
join sysobjects o
on p.id = o.id
where objectproperty(o.id,'IsTableFunction') =1
-- script out the changing of column level collation
declare @CC_TableName sysname,
@CC_UserName sysname,
@ColName sysname,
@CC_Length nvarchar(100),
@CC_TypeName sysname,
@CC_OtherText nvarchar(4000),
@CC_NullText nvarchar(100),
@CC_id int
set @C = cursor for
select o.id,
u.name,
o.name as tablename,
c.name as colname,
case when t.name like 'n%' then cast(c.length / 2 as nvarchar(100)) else cast(c.length as nvarchar(100)) end as Length,
t.name as typename,
case when c.isnullable=1 then 'NULL' else 'NOT NULL' end as nullable
from sysobjects o
join syscolumns c
on o.id = c.id
join systypes t
on t.xtype = c.xtype
and t.xusertype = c.xusertype
join sysusers u
on u.uid = o.uid
where o.type ='U'
and objectproperty(o.id,'IsMSShipped')=0
and c.collationid is not null
--and c.collation <> cast(DATABASEPROPERTYEX(DB_NAME(),'collation') as sysname)
open @C
fetch next from @C into @CC_id,@CC_UserName,@CC_TableName, @ColName, @CC_Length, @CC_TypeName,@CC_NullText
while @@Fetch_Status = 0
begin
if @CC_TypeName COLLATE DATABASE_DEFAULT in ('ntext','text')
begin
-- we can not use the alter table statment to change column level collation on text columns
--we need to do each of these as a separate transaction dur to the risks of errors
set @SQLSegment = '
declare @InError bit
set @InError =0
begin transaction
-- add a temp column
exec (''Alter table ['+@CC_UserName+'].['+@CC_TableName+'] add [____temp] [' + @CC_TypeName + ']'')
-- copy data to temp column
if @@error<>0 set @InError =1
if @@error = 0
exec (''update ['+@CC_UserName+'].['+@CC_TableName+'] set [____temp] =[' + @ColName + ']'')
-- readd origional column
if @@error<>0 set @InError =1
if @@error = 0'
-- if default constraint must add it
if exists ( select *
from sysconstraints
where id = @CC_id
and col_name(@CC_id,colid) = @ColName
and (status & 5) = 5 )
-- if there are default constraints add a bit to do that
set @SQLSegment = @SQLSegment +
(select '
exec (''Alter table ['+@CC_UserName+'].['+@CC_TableName+'] drop constraint [' + object_name(c.constid) + ']'')
if @@error<>0 set @InError =1
if @@error = 0
exec (''Alter table ['+@CC_UserName+'].['+@CC_TableName+'] drop column [' + @ColName + ']'')
if @@error<>0 set @InError =1
if @@error = 0
exec ('' ALTER TABLE ['+@CC_UserName+'].['+@CC_TableName+'] ADD ['+o.name+'] [' + @CC_TypeName + '] CONSTRAINT [' + object_name(c.constid) + '] DEFAULT ' + replace(t.text,'''','''''') + ' '')'
from sysconstraints c
join syscolumns o
on c.id = o.id
and c.colid = o.colid
join syscomments t
on t.id = c.constid
where c.id = @CC_id
and (c.status & 5) = 5
and col_name(c.id,c.colid) = @ColName) --default constraint
else
set @SQLSegment = @SQLSegment + '
exec (''Alter table ['+@CC_UserName+'].['+@CC_TableName+'] drop column [' + @ColName + ']'')
-- drop origional column
if @@error<>0 set @InError =1
if @@error = 0
exec (''Alter table ['+@CC_UserName+'].['+@CC_TableName+'] add [' + @ColName+'] [' + @CC_TypeName + '] '')'
set @SQLSegment = @SQLSegment + '
-- Copy data back to origional column
if @@error<>0 set @InError =1
if @@error = 0
exec (''update ['+@CC_UserName+'].['+@CC_TableName+'] set [' + @ColName + '] = [____temp] '')
-- drop temp column
if @@error<>0 set @InError =1
if @@error = 0
exec (''alter table ['+@CC_UserName+'].['+@CC_TableName+'] drop column [____temp]'')
'
if @CC_TypeName = 'NOT NULL'
set @SQLSegment = @SQLSegment + '
-- now set the appropriate nullability
if @@error<>0 set @InError =1
if @@error = 0
exec (''Alter table ['+@CC_UserName+'].['+@CC_TableName+'] Alter column [' + @ColName+'] [' + @CC_TypeName + '] '+@CC_NullText+' '')'
set @SQLSegment = @SQLSegment + '
if @@error<>0 set @InError =1
if @@error = 0
commit transaction
else
rollback transaction
'
insert into #SQL values (@SQLSegment)
end
else
begin
-- normal columns
set @SQLSegment = 'Alter table ['+@CC_UserName+'].['+@CC_TableName COLLATE DATABASE_DEFAULT+'] Alter Column ['+@ColName COLLATE DATABASE_DEFAULT+ '] ['+@CC_TypeName COLLATE DATABASE_DEFAULT+']'
if @CC_TypeName COLLATE DATABASE_DEFAULT in ('nVarchar', 'varchar','char','nchar')
set @SQLSegment = @SQLSegment COLLATE DATABASE_DEFAULT +' ('+@CC_Length COLLATE DATABASE_DEFAULT + ')'
set @SQLSegment = @SQLSegment COLLATE DATABASE_DEFAULT + ' COLLATE DATABASE_DEFAULT ' + @CC_NullText COLLATE DATABASE_DEFAULT + '
'
insert into #SQL values (@SQLSegment)
end
fetch next from @C into @CC_id,@CC_UserName,@CC_TableName, @ColName, @CC_Length, @CC_TypeName,@CC_NullText
end
close @C
deallocate @C
-- script out recreation of check constraints
insert into #SQL
select 'Alter table ['+u.name+'].[' + o.name + '] WITH NOCHECK ADD CONSTRAINT ['+object_name(cs.constid)+'] CHECK '+sc.text + '
' + case when objectproperty(cs.constid,'CnstIsDisabled') = 1 then 'Alter table ['+u.name+'].[' + o.name + '] NOCHECK CONSTRAINT ['+object_name(cs.constid)+']' else '' end
from sysconstraints cs
join syscomments sc
on sc.id = cs.constid
join sysobjects o
on o.id = cs.id
join sysusers u
on u.uid = o.uid
where objectproperty(cs.constid,'IsCheckCnst') = 1
--script out recreation of calculated columns
insert into #SQL
select 'ALTER TABLE ['+u.name+'].['+o.name+'] ADD ['+c.name+'] AS '+sc.text
from syscolumns c
join syscomments sc
on c.id = sc.id
and c.colid = sc.number
join sysobjects o
on o.id = c.id
join sysusers u
on o.uid = u.uid
where c.iscomputed=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsTable')=1
-- script out the recreation of indexes
-- DISPLAY THE RESULTS
insert into #SQL
select case when (stats & 4096)<>0 or (stats & 2048) <> 0 then
--Constraint
'ALTER TABLE '+objectname+' ADD CONSTRAINT ['+index_name+'] '
+ case when (stats & 2048)<>0 then 'PRIMARY KEY ' else 'UNIQUE ' end
+ case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ ' ('+index_keys+')'
+ case when OrigFillFactor >0 then ' WITH FILLFACTOR =' + cast(OrigFillFactor as nvarchar(3)) else @IX_empty end
+ ' ON ['+groupname+']
' collate database_default
when (stats & 64) <> 0 then
--statistics
'CREATE STATISTICS ['+index_name+'] on '+objectname+' ('+index_keys+')'
+ case when (stats & 16777216)<>0 then ' WITH ' else @IX_empty end
+ case when (stats & 16777216)<>0 then @IX_des16777216 else @IX_empty end
else
-- index
'CREATE ' + case when (stats & 2)<>0 then @IX_des2 +' ' else @IX_empty end +case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end +' INDEX'
+ ' ['+ index_name +'] on '+objectname+' ('+index_keys+')'
+ case when OrigFillFactor >0 or (stats & 1) <> 0 or (stats & 16777216) <> 0 then ' WITH ' else @IX_empty end
+ case when OrigFillFactor >0 then 'PAD_INDEX, FILLFACTOR = ' +cast(OrigFillFactor as nvarchar(3) ) else @IX_empty end
+ case when (stats & 1) <> 0 then ', '+ @IX_des1 else @IX_empty end
+ case when (stats & 16777216) <> 0 then ', '+ @IX_des16777216 else @IX_empty end
+ ' ON ['+groupname+']
'
end
from #spindtab
where IsAutoStatistic=0 --do not recreate auto statistics
drop table #spindtab
-- script recreation of foiegn keys
-- script out foreign keys
declare @FK_KeyName sysname,
@FK_TableName sysname,
@FK_ReferencedTable sysname,
@ConstID int,
@Col1 sysname,
@Col2 sysname,
@ColList1 nvarchar(2000),
@ColList2 nvarchar(2000),
@CnstIsUpdateCascade bit,
@CnstIsNotRepl bit,
@CnstIsDeleteCascade bit,
@CnstIsDisabled bit,
@C2 cursor
set @C = cursor for
select '[' + u.name + '].[' + o.name + ']' as TableName,
object_name(constid) as KeyName,
( select distinct '['+fu.name+'].['+ro.name+']'
from sysforeignkeys fk
join sysobjects ro
on ro.id = fk.rkeyid
join sysusers fu
on fu.uid = ro.uid
where fk.constid = c.constid) as ReferencedTable,
constid,
objectproperty(constid,'CnstIsUpdateCascade') CnstIsUpdateCascade,
objectproperty(constid,'CnstIsDeleteCascade') CnstIsDeleteCascade,
objectproperty(constid,'CnstIsNotRepl') CnstIsNotRepl,
objectproperty(constid,'CnstIsDisabled') CnstIsDisabled
from sysconstraints c
join sysobjects o
on c.id = o.id
join sysusers u
on u.uid = o.uid
where objectproperty(constid,'IsForeignKey')=1
and constid in (
select fk.constid
from sysforeignkeys fk
join syscolumns fc
on fc.colid = fk.fkey
and fc.id = fk.fkeyid
join syscolumns rc
on rc.colid = fk.rkey
and rc.id = fk.rkeyid
where fc.collationid is not null
or rc.collationid is not null
or 1=1) --paramater allows all constraints to be dropped
open @C
fetch next from @C into @FK_TableName, @FK_KeyName, @FK_ReferencedTable,@ConstID, @CnstIsUpdateCascade, @CnstIsDeleteCascade, @CnstIsNotRepl,@CnstIsDisabled
while @@fetch_Status =0
begin
set @ColList1 = ''
set @ColList2 = ''
set @C2 = Cursor for
select fc.name,
rc.name
from sysforeignkeys fk
join syscolumns fc
on fc.colid = fk.fkey
and fc.id = fk.fkeyid
join syscolumns rc
on rc.colid = fk.rkey
and rc.id = fk.rkeyid
where fk.constid = @ConstID
open @C2
fetch next from @C2 into @Col1, @Col2
while @@Fetch_status=0
begin
if len(@ColList1) > 0
set @ColList1 = @ColList1 collate database_default+', '
if len(@ColList2) > 0
set @ColList2 = @ColList2 collate database_default+', '
set @ColList1 = @ColList1 collate database_default +'[' + @Col1 collate database_default + ']'
set @ColList2 = @ColList2 collate database_default +'[' + @Col2 collate database_default + ']'
fetch next from @C2 into @Col1, @Col2
end
close @C2
deallocate @C2
set @SQLSegment = 'Alter table '+ @FK_TableName collate database_default + ' WITH NOCHECK ADD CONSTRAINT [' + @FK_KeyName collate database_default + '] FOREIGN KEY ('+@ColList1 collate database_default + ') REFERENCES ' + @FK_ReferencedTable collate database_default+' ('+ @ColList2 collate database_default +')'
if @CnstIsUpdateCascade =1
set @SQLSegment =@SQLSegment + ' ON UPDATE CASCADE'
if @CnstIsDeleteCascade =1
set @SQLSegment =@SQLSegment + ' ON DELETE CASCADE'
if @CnstIsNotRepl =1
set @SQLSegment =@SQLSegment + ' NOT FOR REPLICATION'
set @SQLSegment = @SQLSegment +'
'
insert into #SQL values (@SQLSegment)
if @CnstIsDisabled=1
begin
set @SQLSegment = 'Alter table '+ @FK_TableName + ' NOCHECK CONSTRAINT [' + @FK_KeyName + ']
'
insert into #SQL values (@SQLSegment)
end
fetch next from @C into @FK_TableName, @FK_KeyName, @FK_ReferencedTable,@ConstID, @CnstIsUpdateCascade, @CnstIsDeleteCascade, @CnstIsNotRepl,@CnstIsDisabled
end
close @C
deallocate @C
-- finally set back to multi user access
insert into #SQL values ('alter database [Prism Test] set multi_user')
select * from #SQL order by ID
return
|
|
|
|
|
Hello and thank you. It took 6 hours and 43 minutes to run but it has fixed my issue.
Thanks again.
|
|
|
|
|
What was origional error you mentioned? Were you trying to run on SQL Server 7?
Thanks
s
|
|
|
|
|
I'm a newbie in SQL, I tried to change the database collation (from SQL_Latin1_General_CP1_CI_AS to Latin1_General_Bin) of a particular database (Northwind) not Master database using your tool and I encountered an error.
Alter table [dbo].[Orders] Alter Column [ShipName] [nvarchar] COLLATE DATABASE_DEFAULT NULL
GO
8152 - String or binary data would be truncated.
3621 - The statement has been terminated.
Please advise.
Thanks,
Ruy
|
|
|
|
|
Ah yes there is a small bug - if you look in the Script.SQL file and look for 'nVarchar' (possibly line 485) this should read 'nvarchar'
Let me know if this fixes the issue
Thanks
Alex
|
|
|
|
|
when I checked the script.sql file, there is no 'nVarchar' exists. There is only nvarchar
|
|
|
|
|
Hello,
When I found your script at first I was very happy I could now move DBs from China, Europe or the Middle East, well not so quick.
In the DB after I use the script all Data Types text are NULL, ie they loose their data.
I get the following error on all Text type fields, in the example below WC_REASON is a Text field.
Do you have this working for Data types : Text ?
(ie I am not sure if this is an issue with your application or with my DB)
Any suggestions ?
Thanks
Sandy
Error :
Server: Msg 450, Level 16, State 1, Line 1
Code page translations are not supported for the text data type. From: 1255 To: 1252.
I broke the SQL into chunks and this was given by the following code :
declare @InError bit
set @InError =0
begin transaction
-- add a temp column
exec ('Alter table [dbo].[WORK_CARD] add [____temp] [text]')
-- copy data to temp column
if @@error<>0 set @InError =1
if @@error = 0
exec ('update [dbo].[WORK_CARD] set [____temp] =[WC_REASON]')
-- readd origional column
if @@error<>0 set @InError =1
if @@error = 0
exec ('Alter table [dbo].[WORK_CARD] drop column [WC_REASON]')
-- drop origional column
if @@error<>0 set @InError =1
if @@error = 0
exec ('Alter table [dbo].[WORK_CARD] add [WC_REASON] [text] ')
-- Copy data back to origional column
if @@error<>0 set @InError =1
if @@error = 0
exec ('update [dbo].[WORK_CARD] set [WC_REASON] = [____temp] ')
-- drop temp column
if @@error<>0 set @InError =1
if @@error = 0
exec ('alter table [dbo].[WORK_CARD] drop column [____temp]')
if @@error<>0 set @InError =1
if @@error = 0
commit transaction
else
rollback transaction
|
|
|
|
|
So you are converting data from hebrew to Latin - I guess that some text in the WC_Reason column has some hebrew characters in it that can not be represented using the Latin code page - you can either update your data in the WC_Reason column to contain only latin characters before running the script OR you could make the WC_Reason column use the nvarchar data type.
does this help?
Alex
|
|
|
|
|
Hi, This is a great tool, i used it against a SQL2000 with no error.
Now i want to test it against a SQL2005, and i get this error :
Cannot insert the value NULL into column 'groupname', table 'tempdb.dbo.#spindtab____00...; ... INSERT fails.
Any idea?
Good work,
Thanks.
David
|
|
|
|
|
This is interesting - would it be possible for you to script the schema for your database or at least the part of your database that causes the problem?
Thanks
Alex
|
|
|
|
|
Muy buen trabajo!! (Very Good job!!)
Fernando Desde Argentina
|
|
|
|
|
This utility solved my problem.
You did really a great job !
Régis
|
|
|
|
|
Thanks for a great utility .;)
|
|
|
|
|
Hey.
Well, I'm trying to change the collation of the database used by an accounting program. Each time I excute it I get the following errors:
Server: Msg 5074, Level 16, State 8, Line 2
The object 'IDO_TPIECE' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
The index 'IDO_TTIERS' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
The index 'IDO_TDATE' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
The index 'IDO_TIERS' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
The index 'IDO_DATE' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
The index 'IDO_SOUCHE' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
and so on...
Any idea on how to circumvent this?
|
|
|
|
|
Well... I solved the problem. I had to change the order of the different steps like this:
1. Set db to single user
2. Set arithabort on
3. Drop the indexes
4. Drop the constraints
5. Drop the statistiscs
6. Drop the calculated fields
7. Alter database collation
8. Alter tables/columns collation
9. Recreate the calculated fields
10. Recreate the constraints
11. Recreate the indexes
12. Set artithabort off
13. Set db to multi user
If I change the script to execute in this order it works fine....
|
|
|
|
|
Thanks for the feedback - i have been a little tied up with work recently but will incorporate your recommended resequencing soon.
Alex
|
|
|
|
|
I have now modified the logic following your feedback.
Many thanks
Alex Baker
|
|
|
|
|
I found your gem while researching SQL Server 2000 collations. I have been looking for a tool like this for some time to correct some of the database mistakes that I've made in the past :P Anyways, thanks for the tool
My Collation blog
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
