Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Show all database's tables' columns' collations. Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2008 1:51 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 2:52 AM
Points: 711, Visits: 222
Comments posted to this topic are about the item Show all database's tables' columns' collations.
Post #449074
Posted Friday, March 28, 2008 9:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, April 4, 2008 2:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:28 AM
Points: 1,392, Visits: 1,002
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
Posted Friday, April 4, 2008 2:46 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 2:52 AM
Points: 711, Visits: 222
Hmm ...
Right.
My oversight.
Sorry for that.
Post #479756
Posted Friday, April 4, 2008 3:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:18 PM
Points: 76, Visits: 262
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 »

Add to briefcase

Permissions Expand / Collapse