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



2) Using SQL Server Management Studio

Refer the following two diagram to find out the SQL Collation.
Write Click on Database

Click on Properties

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

33 thoughts on “SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS

  1. Hi to all…

    I am new to SQL. here is my doubt.
    I have a stored procedure in that how can i find the collation errors.

    Example: i am having a query like this, it is a stored procedure in that how can i find collation error is there or not.

    My SP is like this:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    ALTER PROC [dbo].[CC_BTCC_WRITE_QUEUE_ROUTING_T0_XML](@ReqID bigint, @tmpxml nvarchar(4000) out,@QueueID nvarchar(15),@ID as int)
    AS
    BEGIN
    –Write Strategy Routing Rules
    /**** STRATEGY Time Based Routing****/
    Declare @tmpTBRoute nvarchar(1000)
    Declare @QueRoutingID nvarchar(100)
    Declare @QueRoutingType nvarchar(100)

    EXEC CC_BTCC_APPEND_TO_STRATEGY_XML @tmpxml out,N”,@ID

    DECLARE GETSTRATEGY_ROUTING_CURSOR CURSOR FORWARD_ONLY
    FOR
    SELECT N”, ID, RoutingType
    FROM dbo.WRK_CC_Routing
    WHERE ParentID = @QueueID
    AND ReqID = @ReqID
    ORDER BY RoutingMediaType, RoutingType, Behaviour,Name
    –OPEN
    OPEN GETSTRATEGY_ROUTING_CURSOR
    –GET initial
    FETCH NEXT FROM GETSTRATEGY_ROUTING_CURSOR
    INTO @tmpTBRoute ,@QueRoutingID, @QueRoutingType
    –Iterate
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC CC_BTCC_APPEND_TO_STRATEGY_XML @tmpxml out,@tmpTBRoute,@ID

    /**** STRATEGY Queue Routing Forward Numbers ****/
    EXEC CC_BTCC_WRITE_STRATEGY_ROUTING_FORWARD_NUMBERS_T0_XML @ReqID , @tmpxml out, @QueRoutingID, ‘Standard’, @ID
    /*======= STRATEGY Queue Routing Forward Numbers ======*/

    /**** STRATEGY Queue Routing Search List Items ****/
    EXEC CC_BTCC_WRITE_STRATEGY_ROUTING_SEARCH_ITEMS_T0_XML @ReqID, @tmpxml out, @QueRoutingID , @QueRoutingType , @ID
    /**** STRATEGY Queue Routing Search List Items ****/

    –GET Next
    EXEC CC_BTCC_APPEND_TO_STRATEGY_XML @tmpxml out,N”,@ID
    FETCH NEXT FROM GETSTRATEGY_ROUTING_CURSOR
    INTO @tmpTBRoute ,@QueRoutingID, @QueRoutingType
    END
    CLOSE GETSTRATEGY_ROUTING_CURSOR
    DEALLOCATE GETSTRATEGY_ROUTING_CURSOR

    EXEC CC_BTCC_APPEND_TO_STRATEGY_XML @tmpxml out,N”,@ID
    /*======= STRATEGY Time Based Routing ======*/

    END

    When i am executing this SP i am getting commands completed successfully. But some collation error is in this SP.

    In this SP how can i find the collation error is occurs or not.
    Help me friends.

    Waiting for good reply..

  2. The following also works. Little bit less typing but otherwise its returns the same result

    SELECT collation_name FROM sys.databases WHERE name = ‘AdventureWorks’

  3. Pingback: SQL SERVER - 2005 - Find Database Collation Using T-SQL and SSMS - Part 2 Journey to SQL Authority with Pinal Dave

  4. Hi,

    Could you please tell me how to change the collation of an existing database to UTF-16 for working with Cognos.

    If the solution is in series of steps it would be helpful.

    Thanks

  5. Here’s a way to get the server collation (since the server can have a different collation than the db):

    select serverproperty(‘collation’)

  6. Respected Sir,

    how to find whether the installed version of sql server in the local system is client version or Server Version.

  7. In sql 2005 the script is working fine.

    select name, collation_name, * from sys.databases

    Could you please tell me how can i find in sql 2000.

  8. Hi Pinal,
    I’ve quite different problem. In my production DB I’ve recently modified size of one varchar(30) to varchar(256) and then after the related SPs stops functioning and after research work I’ve found that it is because of collation problem.

    So, can you please tell me what execectly it is, what to do in order to solve this issue and do I need to each and every SPs that uses this table..

    Thanks.

  9. Pingback: SQL SERVER – Detecting Database Case Sensitive Property using fn_helpcollations() « Journey to SQLAuthority

    • SELECT name, collation_name
      FROM sys.columns
      WHERE OBJECT_ID IN (SELECT OBJECT_ID
      FROM sys.objects
      WHERE type = ‘U’
      AND name = )
      AND name =

  10. Pingback: SQL SERVER – Collation and Collation Sensitivity – Quiz – Puzzle – 6 of 31 « SQL Server Journey with SQL Authority

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

  12. Pingback: SQL SERVER – Case Sensitive Database and Database User – Fix: Error: 15151 – Cannot find the user , because it does not exist or you do not have permission. « SQL Server Journey with SQL Authority

  13. 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~

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

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s