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)

Zacharie

Run SQL Server Management Studio and run Select @@Verion to check the version of SQL Server. – Griselgriselda

Refer DATABASEPROPERTYEX and SERVERPROPERTY – Philanthropy

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

Janey

This works well for me; note the collation_name can tell you which "code page" is being used, as seen here. For example if the collation_name value is "SQL_Latin1_General_CP1_CI_AS", note that: > CP1 stands for Code Page 1252 – Sanctitude

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.

Channing

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'

Griselgriselda

I receive null as the response. What does it mean? – Zacharie

Did you put the appropriate database name in the query? – Griselgriselda

sys.columns and sys.objects are not valid < SQL Server 2005, I think maybe you have those two backwards. – Janey

found a link with some official information on this (attention: for sql server 2012): technet.microsoft.com/en-us/library/hh230914.aspx – Tegular