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.