SQL SERVER – Detecting Database Case Sensitive Property using fn_helpcollations()

In my recent Office Hours, I received a question on how to determine the case sensitivity of the database.

The quick answer to this is to identify the collation of the database and check the properties of the collation. I have previously written how one can identify database collation. Once you have figured out the collation of the database, you can put that in the WHERE condition of the following T-SQL and then check the case sensitivity from the description.

SELECT *
FROM fn_helpcollations()

The method shown above is the most recommended method and I suggest using the same.

When I was a young DBA, I did not have the patience to follow the above method. I used to do something very simple.

SELECT 1
WHERE 'SQL' = 'sql'

If the above query returns me the result, it means that the database is case-insensitive. Please note that by no means do I suggest using this method; I really recommend using the method fn_helpcollations().

Another interesting suggestion was from Dave Dustin who is SQL Server MVP from New Zealand. He has provided the following script:

SELECT 1
FROM sys.Databases
WHERE name='<databasename>'
AND (collation_name LIKE '%CS%' OR collation_name LIKE '%BIN%')

Insert your database name in the WHERE clause. If the query returns any result, it means the database is case-sensitive.

It’s interesting to see that one simple question can result to three interesting ways to know the answer. Do you know any other method to know the database case sensitivity? Please share it here and I will post it with due credit.

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

About these ads

SQL SERVER – Change Collation of Database Column – T-SQL Script – Consolidating Collations – Extention Script

This document is created by Brian Cidern, he has written this excellent extension to SQL Expert who SQL SERVER – Change Collation of Database Column – T-SQL Script. His scripts are not only extremely helpful to achieve the task of consolidating collations in quick script. His script not only works perfectly but excellent piece of code and logic.

Hats off to you Brian! You can reach Brian at his email address (brians.sql.blog (at) gmail (dot) com) or leave comment here.

Download all scripts and explanation here

About Collation Consolidation

At some time in your DBA career, you may find yourself in a position when you sit back and realize that your database collations have somehow run amuck, or are faced with the ever annoying CANNOT RESOLVE COLLATION message when trying to join data of varying collation settings.  I’m sure there are as many reasons as to how you’ve come to this juncture as there are readers.  Maybe you have DBA’s and instances around the world and the local staff are using their own collations.  Or, maybe you’ve learned the hard way that ALTER DATABASE x SET COLLATION y doesn’t traverse.  Whatever it is, you’re probably here for the one underlying reason.  Manually consolidating collations would be a nightmare.  So, without loading to much fluff, let’s jump right in.

Here is the basic approach:

  1. Identify all the columns which have incorrect collation values.
  2. Identify all objects bound to above columns, including indexes, keys, checks and UDF’s,
  3. Script out all of the above object DDL.
  4. Drop all of the objects.
  5. Update all the columns with the correct collation
  6. Recreate all of the dropped objects using the DDL generated in step 3

This is done with a collection of non-destructive scripts which generate the DDL to perform all of the above.  This generated DDL MUST be saved to scripts for later use on the target databases.  And obviously, you’ll need one set of scripts per database.  If that requires clarification, just stop reading now.

In this article, I give to you the four scripts to do all this and will try to provide as much understanding as possible.  The first script is purely to create a stored procedure which will be repeatedly called.  This proc will need to exist in a database named “DBADB”.  If you want to use a different database, you’ll need to do a search and replace in the first script and the last script since the database name is hard-coded.  I tried to make these somewhat generic, but the only approach that came to mind would be to use OLE to invoke the VBScript engine to prompt you for the name of the database where this stored procedure is located.  Overkill in my opinion.  Ctrl-H is your friend here.

The script to perform step 3 above uses DMO via OLE, so if you’ve disabled the extended stored procedures; sp_OACreate, sp_OASetProperty, sp_OAMethod, etc, you won’t be able to continue until they’ve been enabled.

One final note.  If you’re not a member of the System Administrators role, please don’t try to execute these.  You’ll probably find your account revoked quickly.

Ok, time to roll up your sleeves.

Script 1 – The DDL Generator

This first script is to create a stored procedure.  This procedure must reside in a location that will not interfere with other databases, whether they’re development, test or production.  The SP uses DMO via OLE to capture a buffer dump of the object code.  The result is the same as if you right-clicked an object and selected “Script to new window as…”

The DMO hierarchy of a SQL instance is weird.  Object reference isn’t particularly intuitive.  But, for the most part, objects are contained within the associated table collection.  In DBA language, that would look like

Table 1:M Checks

Table 1:M Keys

Table 1:M Indexes

Database 1:M UserDefinedFunctions

Therefore, to locate a particular object in the DMO hierarchy, you would need to pass in all the required parameters, including the object type.  So, for checks, keys and indexes, you must also pass in the database name and the parent table name.  For functions, only the database name.

sp_scriptObject(

@in_dbName SysName,

@in_objType VarChar(100),

@in_objName VarChar(100),

@in_objSubName VarChar(100) = NULL

)

Now, the good news.  The subsequent scripts will do all of the above automatically, and this script will be called repeatedly.

Implementing this script.

As I mentioned, this script will be called repeatedly.  The script from whence it’s called will need to be modified to make reference to the correct database name.  These scripts have been designed to work with a DBA-use only database called “DBADB”.  Therefore, if you choose to keep this procedure located in a database of a different name, you’ll need to perform a search and replace for all the “DBADB” reference.  Otherwise, you will need to create a database named “DBADB”.

Execute the first script.  This will create the necessary stored procedure.

Script 2 – Drop Dependencies

The name for this script is somewhat deceiving.  It is a non-destructive script and only performs a set of SELECT statements.  Rather, this script generates a collection of DROP [object] statements.

The goal here is to identify all the objects that are bound to columns which have incorrect collation settings.  There is no clean way to pull them all back in a single statement since determining this is handled differently for the various types of objects.  So, they’re all concatenated together with UNION’s.  The orders in which these are executed are as follows:

  1. Table Functions
  2. Unique Constraints
  3. Check Constraints
  4. Indexes

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

It’s performed in this order in attempt to avoid dependency issues.

The result is a collection of DROP statements.  This must then be copy and pasted into a script and saved for later use.  Best to use a naming convention that reflects the name of the database, and that this will be the first script to execute when you’re ready to begin the patching, for example: myDatabase_post-script_1.sql, myDatabase_post-script_2.sql, etc.

You should take note, it is possible that some databases may not have any objects that are bound to columns that need to be updated, so it’s perfectly fine if this script produces no results.

Script 3 – Change Collations

Much like script 2, this script is also non-destructive and will produce a collection of DDL statements to update the column collations.

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

VERY IMPORTANT.  These scripts were originally intended to allow us to consolidate all our collations in a SQL 2000 to SQL 2008 migration.  Therefore, this script WILL produce two lines of code that you may or may not want.

ALTER DATABASE ‘ + DB_NAME() + ‘ SET COMPATIBILITY_LEVEL=100

and

ALTER DATABASE ‘ + DB_NAME() + ‘ COLLATE ‘ + @collation

where @collation is a variable defined at the beginning of the script.

You can alter the original script I’ve provided, or you can simply remove them from the output before saving.

The remaining results will be a collection of statements to alter the column collations:

ALTER TABLE [tbl] ALTER COLUMN [clm] column_spec() COLLATE [new_collation]

Once this has been executed, copy and paste the output into another script file for later use (myDatabase_post-script_2.sql)

Script 4 – Create Dependent Objects

This is where most of the work takes place.

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

Open the script, connect to the desired database and hit the [fire] button.  Again, like those previous, this script is non-destructive, and produces a series of DDL statements.

The first you’ll notice in the output is the “USE DBADB” statement.  It is very important to note here that if the stored procedure (sp_scriptObject) from the very first script is stored in another database, that this is reflected in the output.

The remainder of the output looks like:

EXEC DBADB.dbo.sp_scriptObject…

With all of the required parameters, based on the type and location of the object.  The other thing you may notice here is that the order in which these statements are in, is the exact reverse of the order from the 2nd script; again, with the intent to avoid dependency crashes.

This output now needs to be copy and pasted to a new query window – not to a file.  The output of the new window should be set to text (Query > Results in Text) or (Ctrl-T).  Execute the statements.  Be aware, that this could take a significant amount of time based on the number of objects and how complex they are.

But, in essence, you will be trapping a buffer dump for each object that needs to be dropped.

VERY IMPORTANT.

There may be a case where an object’s DDL is simply too large.  There are limitations in which the VBScript engine stores string data.  I can’t recall exactly what the issue was, nor do I care to look it up again. But basically, the buffer is limited to 8k.  If your object DDL is greater than 8k, an error will be thrown and the object DDL will NOT be trapped.

After this script has finished, you must first scan the output for errors.  If any are found, identify the object which was not scripted and manually script those and paste them into the output.

This output must then be saved to a final script for execution against the target database.

Final Step

In the end, the above work will have produced 3 scripts that now need to be run against the target database.  Now is a good time to clarify that these generated scripts are very destructive.  So, please take your time, test, test and test.  If you choose to do this on a production box, your career could be at stake.  There is never one-size-fits-all solution, so for each scenario, these scripts may required modifications.  These were produced to fix my needs, but I did try to keep them as generic as possible for re-use.

On your target database, execute each of the generated scripts, obviously in the order specified.

The net result will leave your database with each column, along with the database set at your desired collation setting.

The Quick and Dirty Approach

This section is for those who don’t really care to know the details and simply provides an exact step-by-step.

  1. Backup the target database.
  2. Copy the 4 scripts to an empty folder.
  3. Open Pre-Script_1_DROP_DEPENDENCIES.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  4. Open Pre-Script_2_CHANGE_COLLATIONS.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  5. Open Pre-Script_3_CREATE_DEPENDENCIES.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  6. Create a database named “DBADB”
  7. Execute Pre-Script_0_DBADB.sp_scriptObject.sql
  8. Change the working database to the one which requires collation changes.
  9. Execute Pre-Script_1_DROP_DEPENDENCIES.sql
  10. Save the output to a text file named “dbName_post-script_1.sql”
  11. Execute Pre-Script_2_CHANGE_COLLATIONS.sql
  12. Save the output to a text file named “dbName_post-script_2.sql”
  13. Execute Pre-Script_3_CREATE_DEPENDENCIES.sql
  14. Copy the output to a new query window and execute.
  15. Scan the secondary output for errors.  Identify any failed objects and manually script out the DDL.  Paste it into the location where the error occurred.
  16. Now, save this output to a text file named “dbName_post-script_3.sql”
  17. Copy dbName_post-script_1.sql, dbName_post-script_2.sql, and dbName_post-script_3.sql to the target machine.
  18. Connect to the desired target database and execute each of the above post-scripts in numeric order.

Good luck.  Please heed any warnings I may have mentioned.  If you have any questions, certainly send me an email and I will try to answer promptly.  But, also please be patient, I do have a full time job that takes priority.

Brian Cidern
brians.sql.blog (at) gmail (dot) com

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

SQL SERVER – Change Collation of Database Column – T-SQL Script

Just a day before I wrote about SQL SERVER – Find Collation of Database and Table Column Using T-SQL and I have received some good comments and one particular question was about how to change collation of database. It is quite simple do so.

Let us see following example.

USE AdventureWorks
GO
/* Create Test Table */
CREATE TABLE TestTable (FirstCol VARCHAR(10))
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Change the database collation */
ALTER TABLE TestTable
ALTER COLUMN FirstCol VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Database Cleanup */
DROP TABLE TestTable
GO

When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.

Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.

Additionally, if you are looking for solution to SQL SERVER – Cannot resolve collation conflict for equal to operation visit here.

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

SQL SERVER – Find Collation of Database and Table Column Using T-SQL

Today we will go over very quick tip about finding out collation of database and table column. Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales

Today’s script are self explanatory so I will not explain it much.

/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
GO
/* Find Collation of SQL Server Database Table Column */
USE AdventureWorks
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'Address')
AND
name = 'City'

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

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)

SQL SERVER – 2005 Collation Explanation and Translation – Part 2

Following function return all the available collation of SQL Server 2005. My previous article about the SQL SERVER – 2005 Collation Explanation and Translation.

SELECT *
FROM sys.fn_HelpCollations()

Result Set: (only few of 1011 records)
Name Description
Latin1_General_BIN Latin1-General, binary sort
Latin1_General_BIN2 Latin1-General, binary code point comparison sort
Latin1_General_CI_AI Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_CI_AI_WS Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_CI_AI_KS Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_CI_AI_KS_WS Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_CI_AS_WS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_CI_AS_KS Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_CI_AS_KS_WS Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Latin1_General_CS_AI Latin1-General, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_CS_AI_WS Latin1-General, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_CS_AI_KS Latin1-General, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_CS_AI_KS_WS Latin1-General, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_CS_AS_WS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_CS_AS_KS Latin1-General, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_CS_AS_KS_WS Latin1-General, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive

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

SQL SERVER – Cannot resolve collation conflict for equal to operation

Cannot resolve collation conflict for equal to operation.

In MS SQL SERVER, the collation can be set at the column level. When compared 2 different collation column in the query, this error comes up.

SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col = AccountsTable.Collation2Col

If columns ItemsTable.Collation1Col and AccountsTable.Collation2Col have different collation, it will generate the error “Cannot resolve collation conflict for equal to operation“.

To resolve the collation conflict add following keywords around “=” operator.

SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT
= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

Watch this quick video for resolution:

Collation can affect following areas:

1) Where clauses

2) Join predicates

3) Functions

4) Databases (e.g. TempDB may be in a different collation database_default than the other databases some times)

Checkout following video how collation can change the order of the column:

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