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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy