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

Question

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

Answer 1

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...

Answer 2

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';