Technical Article

Show all database's tables' columns' collations.

,

Purpose Created view displays all text columns' collations in the current database, with the name oftable which contains the column andwith the information whether the collation is different from the database's one

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

Rate

2 (2)

Share

Share

Rate

2 (2)