SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS

This article is written based on feedback I have received on SQL SERVER – Cannot resolve collation conflict for equal to operation. Many reader asked me how to find collation of current database. There are two different ways to find out SQL Server database collation.

1) Using T-SQL (My Recommendation)

Run following Script in Query Editor
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;
ResultSet:
SQLCollation
————————————
SQL_Latin1_General_CP1_CI_AS

SQL SERVER - 2005 - Find Database Collation Using T-SQL and SSMS collation2

2) Using SQL Server Management Studio

Refer the following two diagram to find out the SQL Collation.
Write Click on Database
SQL SERVER - 2005 - Find Database Collation Using T-SQL and SSMS collation

Click on Properties
SQL SERVER - 2005 - Find Database Collation Using T-SQL and SSMS collation1

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Difference and Explanation among DECIMAL, FLOAT and NUMERIC
Next Post
SQL SERVER – 2005 – Find Database Status Using sys.databases or DATABASEPROPERTYEX

Related Posts

27 Comments. Leave new

  • Is there an easy way to change the collation of the server?

    I installed SQL2008R2 to my new laptop but it hasn’t the collation I need for my databases. On my old laptop it was ‘Latin1_General_CI_AS’ and on my new one it is ‘SQL_Latin1_General_CP1_CI_AS’.

    All my old databases I’ve attached, but when I create a temp table I get error messages that the collations are not the same. So SQL can’t compare the varchar fields that I use to make a join.

    Regards,
    Frank Meijer

    Reply
  • regarding collation to find out case sensitive or in-sensitive, can we any other options in the place of CS or CI

    Reply
  • Hi All,

    I have a database in MSSQL Server 2008 that have 3 tables.

    There is a column in each table that contains data in Hindi’s KurtiDev Font (non-unicode),

    I want to convert that data into Unicode form.

    please suggest me any tool or process to do my task.

    Thanks in advance….

    ~S~

    Reply
  • how to check what changed in db ?

    Reply
  • John Ardmore
    July 17, 2012 1:15 pm

    hi,

    Here in Greece, we have a huge problem with data inserted by users.
    e.g. the user writes the name “ΑΛΕΞΑΝΔΡΟΣ” in a field.
    The problem is that the first “A” is English, the rest in Greek.
    Capital letter “A” ( like many other letters in English ) are visually similar,
    but in an sql SELECT statement we don’t know what to write to find anything.

    Is there any way to find character sequence that contain two or more different “charsets” ? the fields are NVARCHAR

    thank you

    Reply

Leave a Reply

Menu