Post reply

Table Collation

  • February 22, 2005 at 6:11 pm

    #61533

    Hi,

     

    I'm fairly new to SQL - Is there a script that I can use to see which tables have the 'Use Collation' implemented?

     

    Thanks

     

    Steve

  • February 22, 2005 at 6:40 pm

    #543592

    Try something like

    select distinct object_name(a.id) table_name, b.collation

     from syscolumns a inner join systypes b on a.xusertype = b.xusertype

     where a.collationid = b.collationid

     and not exists(select * from sysobjects c where a.id = c.id and c.xtype = 's')

     order by 1

    It lists all table collation combination.

  • February 22, 2005 at 6:51 pm

    #543593

    Thanks for the info - works great.

    Is there a script that I can use that will tell me which columns are using a 'COLLATE" clause?

    Thanks

    Steve

  • February 22, 2005 at 8:56 pm

    #543600

    Hai,

    Simply modify the above query

    select  name as column_name , collation collation_name ,id from syscolumns where id in (select id from sysobjects where name ='thetable') and collation is not null

     

     


    Helen
    --------------------------------
    Are you a born again. He is Jehova Jirah unto me

  • February 23, 2005 at 1:14 am

    #543622

    select object_name(a.id) table_name, a.name, b.collation

     from syscolumns a inner join systypes b on a.xusertype = b.xusertype

     where a.collationid = b.collationid

     and not exists(select * from sysobjects c where a.id = c.id and c.xtype = 's')

     order by 1, 2

  • February 23, 2005 at 8:08 am

    #543711

    You could alos try:

    SELECT Table_Name, Column_Name, Data_Type ,Collation_Name

    FROM information_schema.COLUMNS

    WHERE Collation_Name IS NOT NULL

     

  • February 23, 2005 at 5:00 pm

    #543812

    A recommended way.

    Thanks

  • February 23, 2005 at 5:17 pm

    #543814

    Hi There,

    Thanks for all your reply's - they've been really useful.

    Thanks

    Steve

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply