January 29, 2008 at 1:51 pm
Comments posted to this topic are about the item Show all database's tables' columns' collations.
March 28, 2008 at 9:22 pm
i awant all the table in database
April 4, 2008 at 2:31 am
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
April 4, 2008 at 2:46 am
Hmm ...
Right.
My oversight.
Sorry for that.
April 4, 2008 at 3:20 am
to get the collation of a column of a table
select COLLATION_NAME
from information_schema.columns
-- you complete this bit as necessary
where table_name like '%'
and column_name like '%'
or have I missed something?
pcd
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply