How I can obtain the collation of a specific table

Question

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

Answer 1

Just as a point of note. SSMS can show column collations when scripting out the table. You can go into Tools-> Options. Under SQL Server Object Explorer -> Scripting, there is an option to show collation when scripting out a table. You can turn this on temporarily and script out a table to see table creation code including column collations.

Answer 2

There is no such thing as a collation for a table.

A database has a default collation (which defaults to the collation for the server).

The default collation for the database will be applied to any column you add to a table, UNLESS you explicitly specify a collation at the column level.

Answer 3

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'