Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identify SQL Server Instance and Database Collation Using T-SQL and SSMS


By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | More > Database Administration

Attend these FREE MSSQLTips webcasts >> click to register


Problem

How can I find out the collation used by the SQL Server instance and database on my SQL Servers? In this tip we look at different ways by which one can identify the collation used by a database and a SQL Server instance.

Solution

In this tip we will take a look at different ways by which you can easily identify the collation used by a database and at a SQL Server instance level. This tip will cover the following topics:

1. Identify SQL Server database collation using T-SQL
2. Identify SQL Server database collation using SQL Server Management Studio
3. Identify SQL Server instance collation using T-SQL
4. Identify SQL Server instance collation using SQL Server Management Studio

Let's take a look at each of the above options in detail.


Identify SQL Server Database Collation Using T-SQL

A Database Administrator (DBA) can execute the below T-SQL script to quickly identify the collation used by the AdventureWorks database.

/* Identify Collation for a SQL Server database */
DECLARE @DatabaseName as SYSNAME
SET @DatabaseName = 'AdventureWorks'  /* Replace Database Name Here....*/
SELECT 
 DB_NAME(DB_ID(@DatabaseName)) AS DatabaseName
,DATABASEPROPERTYEX(@DatabaseName, 'Collation') AS CollationUsedBySQLServerDatabase
GO

sql server database administrator

The T-SQL script below shows the collation for all of the databases on the current SQL Server instance.

/* Collation used by all the databases on a SQL Server instance */
USE Master
GO
SELECT
 NAME, 
 COLLATION_NAME
FROM sys.Databases
 ORDER BY DATABASE_ID ASC
GO

t-sql script

The T-SQL script below lists all the collations supported by SQL Server 2005 and above versions.

/* Returns a list of all the collations supported by SQL Server 2005 and above */
SELECT * FROM fn_helpcollations() 
GO

The T-SQL script below will provide all of the details about a particular collation.

SELECT * FROM fn_helpcollations() 
WHERE NAME = 'SQL_Latin1_General_CP1_CI_AS'
GO

sql server database collation

Identify SQL Server Database Collation Using SQL Server Management Studio

1. Connect to SQL Server Database Instance using SQL Server Management Studio

2. In Object Explorer navigate to root | Databases | AdventureWorks, right click on the AdventureWorks databases and then select the Properties option from the drop down list as shown in the snippet below.


sql server database instance

3. In the Database Properties window, in the left pane the General Page will be selected by default. You will be able to see the Collation used by the database under the Maintenance section as highlighted in the below snippet.

database properties


Identify SQL Server Instance Collation Using T-SQL

The T-SQL script below will identify the Collation used by the current SQL Server instance.

/* Identify SQL Server Collation Settings*/
USE Master
GO
SELECT SERVERPROPERTY('collation') AS SQLServerCollation
GO
identify sql server instance collation using t-sql

Identify SQL Server Instance Collation Using SQL Server Management Studio

1. Connect to a SQL Server Database Instance using SQL Server Management Studio

2. In Object Explorer, right click SQL Server Instance and then select the Properties option from the drop down list as shown in the snippet below.

sql server management studio

3. In the Server Properties window, in the left pane the General Page will be selected by default. You will be able to see the Server Collation as highlighted in the below snippet.

server properties window
Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, October 18, 2011 - 11:21:24 AM - Anna Back To Top

The default installation of a new SQL server instance in U.S is SQL_Latin1_General_CP1_CI_AS.

But I read from some articles it recommend to use Latin1_General_CI_AS as common installation.

This is easily caused a mixed installlation in an enviroment. My questions is why recommended server collation is Latin1_General_CI_AS, but microsoft default in U.S is SQL_Latin1_General_CP1_CI_AS?

 

Thanks


Tuesday, October 18, 2011 - 4:33:36 AM - Mike Back To Top

Thanks Ashish/Jugal. I got the collation as mentioned.


Tuesday, October 18, 2011 - 3:10:23 AM - Jugal Shah Back To Top

You have to choose sort order 40. From the list box you have to select Binary order, for use with the 850 (Multilingual) character set.

Regards, Jugal Shah

 

 


Tuesday, October 18, 2011 - 3:02:05 AM - Mike Back To Top

I am unable to find SQL_Latin1_General_Cp850_BIN collation in the drop down or in the list box.

Thanks,

Mike


Tuesday, October 18, 2011 - 2:08:48 AM - Ashish Kumar Mehta Back To Top

Hi Mike,

While Installing SQL Server 2005 you need to choose SQL_Latin1_General_Cp850_BIN as collation rather than the default collation suggested by the SQL Server 2005 Installation wizard.

You can choose the "SQL_Latin1_General_Cp850_BIN" collation settings under the "Collation Settings” screens "collation designator ans sort order" drop down. 
As per the below link SQL_Latin1_General_Cp850_BIN collation is supported in sQL Server 2005.

link: http://msdn.microsoft.com/en-us/library/ms144250(v=SQL.90).aspx

Hope this helps!

Thanks
Ashish


Tuesday, October 18, 2011 - 1:32:16 AM - Mike Back To Top

Hello Ashish,

Can you please let me know how can I select SQL_Latin1_General_CP850_BIN colation while SQL Server 2005 installation?

if possible can you please post your Bio and picture.

Thanks,

Mike


Learn more about SQL Server tools