Click here to Skip to main content
14,603,215 members
Articles » Database » Database » SQL Server » Downloads

SQL Server 2000 Collation Changer

Rate this:
4.88 (99 votes)
Please Sign up or sign in to vote.
4.88 (99 votes)
3 Mar 2008CPOL
Change collation order for all text columns in a database
declare @c1 cursor,
		@c2 cursor,
		@stats_id int,
		@object_id int,
		@schema_id int,
		@stats_name sysname,
		@object_name sysname,
		@no_recompute bit,
		@column_list nvarchar(max),
		@sql nvarchar(max)

set @c1 = cursor for
select	s.object_id,
		o.schema_id,
		s.stats_id,
		o.name,
		s.name,
		s.no_recompute
from	sys.stats s
join	sys.objects o
on		s.object_id = o.object_id
where	(exists (
				--find any columns that have a collation specified
				select	c.object_id
				from	sys.stats_columns sc
				join	sys.columns c
				on		sc.object_id = c.object_id
				and		sc.column_id = c.column_id
				where	collation_name is not null
				and		sc.object_id = s.object_id
				and		sc.stats_id = s.stats_id
				) 
				--{2} is the rebuild indexes option from application
				OR {2} = 1)
and		o.is_ms_shipped = 0
and		s.user_created=1

open @c1
fetch next from @c1 into @object_id,@schema_id, @stats_id, @object_name, @stats_name, @no_recompute
while @@fetch_status=0
begin

	set @column_list = ''

	set @c2 = cursor for
	
	select		'['+c.name+']' as definition
	from		sys.stats_columns sc
	join		sys.columns c
	on			sc.object_id = c.object_id
	and			sc.column_id = c.column_id
	where		sc.object_id =@object_id
	and			sc.stats_id = @stats_id
	order by	sc.stats_column_id

	open @c2
	
	fetch next from @c2 into @sql
	while @@fetch_status=0
	begin
		if len(@column_list) >0
			set @column_list = @column_list+', '
		
		set @column_list = @column_list +@sql
		

		fetch next from @c2 into @sql 
		
	end

	close @c2
	deallocate @c2

	set @sql = 'CREATE STATISTICS ['+@stats_name+'] ON [' + SCHEMA_NAME(@schema_id) + '].['+@object_name +'] ('+@column_list+')'
	if @no_recompute=1
		set @sql = @sql + ' WITH NORECOMPUTE'

	insert into #sql(sql) values(@sql)

	fetch next from @c1 into @object_id,@schema_id, @stats_id, @object_name, @stats_name, @no_recompute
end
close @c1
deallocate @c1

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Alex Baker
Software Developer RXP Services
Australia Australia
No Biography provided

Comments and Discussions

Stats

571.5K views
9.7K downloads
101 bookmarked