Below query can be used to list all table columns and their corresponding collation.

USE <DB_NAME> /** replace your database name here **/
GO
SELECT @@servername 'Server',db_name() 'Database',
t.name as "TableName",
c.name as "ColumnName",
c.Collation_name
FROM sys.columns c INNER JOIN sys.tables t
ON t.object_id = c.object_id
WHERE c.object_id
IN (SELECT object_id
FROM sys.objects
WHERE type = 'U')
AND c.collation_name != 'NULL'
ORDER BY t.name, c.name

Query Result:

Below command can be used to check collation of specific object in a database.

USE <DB_NAME> /** replace your database name here **/
GO
SELECT @@servername as 'Server',db_name() as 'Database', name as 'ColumnName', Collation_name FROM sys.columns
WHERE object_id = OBJECT_ID('Student') /** replace your table name here **/

Sample Result:

Below command can be used to alter column collation.

USE <DB_NAME> /** replace your database name here **/
GO
ALTER TABLE <Table_Name> ALTER COLUMN <Column_Name> COLLATE collation_type;