determining the character set of a table / database?

sql-server sql-server-2008-r2 collation
shealtiel picture shealtiel · Sep 6, 2011 · Viewed 90.6k times · Source

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

Aaron Bertrand picture Aaron Bertrand · Sep 6, 2011

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

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More
CREATE TABLE IF NOT EXISTS equivalent in SQL Server

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Read More