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)

SQL Collation, SQL Scripts
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

  • respecte sir can you tell me the code for selecting every n”th row of a table in sqlserver2005

    Reply
  • Saravanan,

    You can use Row_Number() function to perform your request.

    Regards,
    Pinal Dave ( https://blog.sqlauthority.com/ )

    Reply
  • 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..

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

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

    Reply
  • Hi,

    How can I find collation of my csv file.
    Thanks

    Reply
  • how can i change the Current Collation via T-SQL ?

    Reply
  • 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

    Reply
  • Quite helpful.

    Reply
  • Nice discussion about Collation.

    Reply
  • I’m getting scalar variable errors when I am declaring the statements can an incrrect collation cause this?

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

    select serverproperty(‘collation’)

    Reply
  • pankaj balooni
    April 1, 2010 2:48 pm

    how can i list out all the DBs and there respective collation settings for an instance?

    Reply
  • Hello Pankaj,

    Use the below query:

    select name, collation_name, * from sys.databases

    Regards,
    Pinal Dave

    Reply
  • Thanks for your post it was very helpful!

    Reply
  • sairamakrishna
    May 9, 2011 11:25 am

    Respected Sir,

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

    Reply
  • Siva Krishna
    June 9, 2011 12:06 pm

    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.

    Reply
  • 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.

    Reply
  • How to change Default Collation to UTF_8 in database

    Reply
  • Hi…

    How can i Find what type of database installed in my system (eg: sql server2005, 2008, oracle,..)

    Reply
  • Hi,

    How to check collation information at cloumn level in table, Is there any query?

    Regards,

    Alok

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

      Reply

Leave a Reply