How to view collation for a specific column of a specific table

Sudip Bhatt 2,271 Reputation points
2020-10-06T20:07:29.123+00:00

I tried this one to see the collation of column LineItem of a specific table called TblLineItemTemplate but no luck.
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'lineitem')
AND name = 'TblLineItemTemplate'

please a script which help me to see collation for a specific column of a specific table. thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 98,911 Reputation points
    2020-10-06T22:05:45.263+00:00

    I would guess then that you mispelled any of the names. I tried your query in a database I have, with different names of course, and I got the result back.

    However, Normally I would write the query as:

    SELECT name, collation_name
    FROM sys.columns
    WHERE object_id = Object_id('Orders')
      AND name = 'CustomerID'
    

    Or I would be lazy and simply use sp_help...

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,561 Reputation points
    2020-10-06T21:10:38.653+00:00

    Please try the following T-SQL. The COLLATION_NAME column will show the collation.

    SQL

    USE pubs;
    GO
    
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_CATALOG = 'pubs'
       AND TABLE_SCHEMA = 'dbo'
       AND TABLE_NAME = 'authors'
       AND COLUMN_NAME = 'address';
    
    1 person found this answer helpful.
    0 comments No comments