Check Collation differences and Report – DBA

Leave a comment (0) Go to comments

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

At times, DBA need to know about collation level at database level, server level or at column level prior to perform some task. Here is the script which will list down

  1. All Databases where collation is different from server setting
  2. All Columns where collation is different from database setting
 

/* Script to compare all Collation settings

Returns 2 results sets


1) Databases where collation is different from server setting

2) Columns where collation is different from database setting

*/

 

 

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#CollationComparison')) DROP TABLE #CollationComparison

 

CREATE TABLE #CollationComparison

(Database_Name SYSNAME

,Table_Schema SYSNAME

,Table_Name SYSNAME

,Column_Name SYSNAME

,Server_Collation SYSNAME

,Database_Collation SYSNAME

,Column_Collation SYSNAME)

 

DECLARE @SQL NVARCHAR(MAX)

 

DECLARE @dbname NVARCHAR(200)

DECLARE dbcursor CURSOR FOR

select name from sys.databases

OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @dbname

 

WHILE @@FETCH_STATUS = 0

BEGIN

print @dbname

 

SET @SQL = 'INSERT INTO #CollationComparison

(Database_Name

,Table_Schema

,Table_Name

,Column_Name

,Server_Collation

,Database_Collation

,Column_Collation)

 

SELECT

C.TABLE_CATALOG AS DATABASE_NAME

,C.TABLE_SCHEMA

,C.TABLE_NAME

,C.COLUMN_NAME

,CONVERT(VARCHAR,SERVERPROPERTY(''COLLATION'')) AS SERVER_COLLATION

,CONVERT(SYSNAME,DATABASEPROPERTYEX(D.NAME,''COLLATION'')) AS DATABASE_COLLATION

,C.COLLATION_NAME AS COLUMN_COLLATION

FROM [‘ + @dbname + ‘].INFORMATION_SCHEMA.COLUMNS C

INNER JOIN SYS.DATABASES D

ON DB_ID(C.TABLE_CATALOG) = DB_ID(D.NAME)

WHERE DATA_TYPE IN (''VARCHAR'' ,''CHAR'',''NVARCHAR'',''NCHAR'',''TEXT'',''NTEXT'')

'

 

exec sp_executesql @SQL

print @sql

FETCH NEXT FROM dbcursor INTO @dbname

END

 

CLOSE dbcursor

DEALLOCATE dbcursor

 

SELECT DISTINCT Server_Collation,Database_Collation,Database_Name FROM #CollationComparison WHERE Server_Collation <> Database_Collation

 

SELECT DISTINCT * FROM #CollationComparison WHERE Column_Collation <> Database_Collation

Script OUTPUT

Check Collation differences and Report   DBA sql server collation settings sql server collation server database table column sql server Collation difference database server column sql server Collation Difference SQL Server Collation Script to compare all Collation setting report collation setting object level collation investigate column Databases collation different collation setting report Databases where collation is different from server collation Database collation Column where collation is different from database collation Column collation Collation Differences check database collation check collation settings check collation differences

EOF - Check Collation differences and Report – DBA, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.