sqlsql-servercollation

How I can obtain the collation of a specific table in a database?

How I can obtain the collation of a specific table in a database? Is it possible that a table have different collation in db?

Solution

  • Collation at the table level is on a per column basis, so it is possible to have a collation different than the database. If the collation is not defined at the column level, it defaults to the database collation setting.

    SQL Server 2000:

    SELECT c.name, 
       c.collation 
  FROM SYSCOLUMNS c
 WHERE [id] = OBJECT_ID('your_table_name')

    SQL Server 2005+:

    SELECT c.name, 
       c.collation_name
  FROM sys.columns c
  JOIN sys.tables t ON t.object_id = c.object_id
 WHERE t.name = 'your_table_name'