How to generate T-SQL scripts in SQL Server Management Studio with the collation settings

By:   |   Comments   |   Related: > SQL Server Management Studio Configuration


SQL Server collation is an important setting when creating database objects. Once the SQL Server collation has been configured for the instance or database, it is not simple to update the collations and even after fixing collations there can be performance or other unexpected issues. One of the best ways to ensure that collation issues do not happen is to ensure that the collation settings are properly specified in the script that generates the database objects. SQL Server Management Studio (SSMS) by default does not include scripting collation settings. In this tip we will demonstrate how to include the collation settings.


SSMS provides options to control the script generation behavior, which can be configured to generate collation settings while generating scripts.

In this tip we will create a blank database with specific collation settings and generate a script using SSMS for these objects. After that we will modify the script generation setting in SSMS and compare the scripts for differences. Follow the steps below.

 Open SSMS, and create a new database by right-clicking on the database folder in object explorer and selecting the "New Database" menu option. Select a specific collation "Latin1_General_BIN2" as shown below for the new database and click "OK" to create the database.

New Database SQL Server Collation

 Right-click on the newly created database, and select Script Database As > Create To > New Query Editor Window. This will generate the script as shown below. Unless you knew there was a collation difference, you probably wouldn't even notice that the collation settings for the CREATE DATABASE are missing in the script.

Default Script without the SQL Server Collation

If you execute this script and create the database, when you check the properties of the database, you will notice that the database has been created with the default collation settings inherited from the server level collation. This becomes a problem in the future as the expected collation is different. Those who are not experienced with this behavior of SSMS would not notice this until it becomes an issue.

New Database with the default SQL Server collation

It is very easy to fix this scripting behavior. Navigate to the Tools > Options menu in SSMS. Search for scripting options, which you will find under the SQL Server Object Explorer category. You will notice that by default, the "Include collation" setting property is set to "False", which means the collation settings will not be included during script generation. Change the value of this property to "True". You can also change other properties as required. For example, you may want to have the script include checks for the existence of the database object that you intend to create. For this setting you can change the "Check for object existence" property to "True" which by default has the value set to "False".

Scripting Options for SQL Server Collation

Generate the script again by right-clicking on the database, and select Script Database As > Create To > New Query Editor Window. This will generate the script as shown below. You will notice that this time the collation settings are included in the script in the CREATE DATABASE section.

T-SQL script with the correct SQL Server collation

With these steps, the script generation behavior will ensure collation and other important settings are included in the script generation process.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms