Show all database's tables' columns' collations.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rafal S. » Show all database's tables' columns'...

Show all database's tables' columns' collations.

Rate Topic

Display Mode

Topic Options

Author

Message

skra

Posted Tuesday, January 29, 2008 1:51 PM

Say Hey Kid

Group: General Forum Members
Last Login: Thursday, May 09, 2013 12:41 AM
Points: 711, Visits: 209

Comments posted to this topic are about the item Show all database's tables' columns' collations.

Post #449074

lavkushsharma

Posted Friday, March 28, 2008 9:22 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2008 9:10 PM
Points: 1, Visits: 0

i awant all the table in database

Post #476542

henrik staun poulsen

Posted Friday, April 04, 2008 2:31 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:48 PM
Points: 1,130, Visits: 852

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

Post #479749

skra

Posted Friday, April 04, 2008 2:46 AM

Say Hey Kid

Group: General Forum Members
Last Login: Thursday, May 09, 2013 12:41 AM
Points: 711, Visits: 209

Hmm ...
Right.
My oversight.
Sorry for that.

Post #479756

pcd_au

Posted Friday, April 04, 2008 3:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:45 PM
Points: 42, Visits: 168

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

Post #479775

« Prev Topic | Next Topic »

Permissions