determining the character set of a table / database?

Question

What T-SQL command can be run to find character set of a table or database in SQL Server?

edit: Server version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

Answer 1

You can check the version using

SELECT @@VERSION;

It it's 9.00 or greater, you can check the collation of a column using

SELECT collation_name FROM sys.columns 
WHERE name = 'column name'
AND [object_id] = OBJECT_ID('dbo.table name');

And for the database using

SELECT collation_name FROM sys.databases 
WHERE name = 'database name';

If it's < 9.0 then you're using SQL Server 2000 or lower. For 2000 I believe you can check similar columns (e.g. syscolumns.collationid for columns).

Answer 2

The character set depends on the data type of a column. You can get an idea of what character sets are used for the columns in a database as well as the collations using this SQL:

select data_type, character_set_catalog, character_set_schema, character_set_name, collation_catalog, collation_schema, collation_name, count(*) count
from information_schema.columns
group by data_type, character_set_catalog, character_set_schema, character_set_name, collation_catalog, collation_schema, collation_name;

If it's using the default character set, the character_set_name should be iso_1 (ISO 8859-1) for the char and varchar data types. Since nchar and nvarchar store Unicode data in UCS-2 format, the character_set_name for those data types is UNICODE.

Answer 3

To check the Collation of SQL Server run this in SQL Server Management Studio (put your database name in the appropriate place)

SELECT DATABASEPROPERTYEX('DatabaseNameGoeshere', 'Collation') DatabaseCollation;

Note that Collation settings can be set each level

Server

SELECT SERVERPROPERTY('Collation')  as ServerCollation

Database

SELECT DATABASEPROPERTYEX('DatabaseNameGoeshere', 'Collation') DatabaseCollation;

Column (SQL Svr 2005 or higher)

Select TABLE_NAME, COLUMN_NAME, Columns.COLLATION_NAME
From INFORMATION_SCHEMA.COLUMNS

Column (lower than SQL Svr 2005)

SELECT name, collation_name
FROM syscolumns
WHERE OBJECT_ID IN 
(
    SELECT OBJECT_ID
    FROM sysobjects
    WHERE type = 'U'
    AND name = 'TableNameGoesHere'
)
AND name = 'ColumnNameGoesHere'