Show all database's tables' columns' collations.

/********************************************************************************************
* 
* 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 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

-- exaple, how to use:

select * from dbo.view_show_tables_collations