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)