create procedure CollationCheck_prc

as

declare @msg varchar(2000)
declare @master_collation varchar(255)

SELECT @master_collation=convert(sysname,DatabasePropertyEx(‘master’,’Collation’))

SELECT ‘the ‘ + name + ‘ database has a different collation from master, ‘ +
convert(sysname,DatabasePropertyEx(name,’Collation’)) + ‘ (master=’ + @master_collation+’)’
as ‘server/database collation check’
into #temp_collations
from sysdatabases
where convert(sysname,DatabasePropertyEx(‘master’,’Collation’)) <>
convert(sysname,DatabasePropertyEx(name,’Collation’))
order by name

if @@rowcount = 0
begin
select ‘there are no databases that have a different collation from master’
as ‘server/database collation check’
end
else
begin
select * from #temp_collations
end

set nocount on

create table #databases (dbid int identity(1,1), dbname varchar(100), collation varchar(100))
create table #database_collations (dbname varchar(100), collation varchar(100))
create table #database_collations_by_column (dbname varchar(100), colname varchar(100), collation varchar(100))

declare @number_of_dbs int, @counter int, @sql varchar(8000), @dbname varchar(100)

insert into #databases
select name, convert(sysname,DatabasePropertyEx(name,’Collation’)) as collation
from master..sysdatabases
order by name

select @number_of_dbs = count(*) from #databases
select @counter=1

while @counter <= @number_of_dbs
begin

select @dbname = dbname from #databases where dbid=@counter

select @sql= ‘insert into #database_collations select ”’ + @dbname + ”’ as dbname, sc.collation from ‘ +
@dbname + ‘..syscolumns sc, ‘ + @dbname + ‘..sysobjects so, ‘ + @dbname + ‘..systypes st
where so.id=sc.id
and so.type=”U”
and st.xtype=sc.xtype
and sc.xtype in (select xtype from systypes
where name in (”char”,”nchar”,”nvarchar”,”varchar”))
and so.name not like ”dt%”
group by sc.collation’

exec (@sql)

select @sql= ‘ insert into #database_collations_by_column select ”’ + @dbname +
”’ as dbname, sc.name , sc.collation ‘+
‘ from ‘ +
@dbname + ‘..syscolumns sc, ‘ + @dbname + ‘..sysobjects so, ‘ + @dbname + ‘..systypes st
where so.id=sc.id
and so.type=”U”
and st.xtype=sc.xtype
and sc.xtype in (select xtype from systypes
where name in (”char”,”nchar”,”nvarchar”,”varchar”))
and so.name not like ”dt%”
group by sc.name, sc.collation’

exec (@sql)

select @counter=@counter+1

end

drop table #databases
drop table #database_collations
drop table #database_collations_by_column