Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Updated: 2016-09-13   |   Comments   |   Related: More > SQL Server Management Studio

Problem

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.

Solution

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


Last Updated: 2016-09-13


get scripts

next tip button



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

View all my tips
Related Resources




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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools