I had to add COLLATE Database_Default, in order to get it to work.

As in:

/********************************************************************************************

*

* Author Rafal Skotak

* Purpose Created view displays all text columns' collations in the current database,

* with the name of table which contains the column andwith the information whether

* the collation is different from the database's one

* Date 2008-01-22

*/

if exists(select * from sys.objects where object_id = object_id('dbo.view_show_tables_collations') and type = 'V')

drop view dbo.view_show_tables_collations

go

create view dbo.view_show_tables_collations

as

select top 9223372036854775807 /* bigint maximum value; to cheat SS2005 You can try also 99.999999999999999999999999 percent */

ss.name as schema_name,

so.name as table_name,

sc.name as column_name,

st.system_type_id,

st.name as type_name,

db_collation.collation_name as database_collation_name,

sc.collation_name,

case

when db_collation.collation_name = sc.collation_name COLLATE database_default then 0

else 1

end as collation_flag

from

sys.objects as so inner join

sys.columns as sc on

so.object_id = sc.object_id inner join

sys.types as st on

sc.system_type_id = st.system_type_id inner join

sys.schemas as ss on

ss.schema_id = so.schema_id cross join

(select collation_name from master.sys.databases where database_id = db_id()) as db_collation

where

so.type = 'U' and

st.system_type_id in (35 /* text */, 99 /* ntext */, 167 /* varchar */, 175 /* char */, 231 /* nvarchar, sysname */, 239 /* nchar */)

order by

collation_flag desc,

schema_name,

table_name,

column_name

go

-- example, how to use:

select * from dbo.view_show_tables_collations