Show all database's tables' columns' collations.

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

  • i awant all the table in database

  • 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

  • Hmm ...

    Right.

    My oversight.

    Sorry for that.

  • 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