THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

List columns where collation doesn't match database collation

Below script lists all database/table/column where the column collation doesn't match the database collation. I just wrote it for a migration project and thought I'd share it. I'm sure lots of tings can be improved, but below worked just fine for me for a one-time execution on a number of servers.

IF OBJECT_ID('tempdb..#res') IS NOT NULL DROP TABLE #res
GO

DECLARE
@db sysname
,@sql nvarchar(2000)

CREATE TABLE #res(server_name sysname, db_name sysname, db_collation sysname, table_name sysname, column_name sysname, column_collation sysname)

DECLARE c CURSOR FOR
SELECT
name FROM sys.databases WHERE NAME NOT IN('master', 'model', 'tempdb', 'msdb') AND state_desc = 'ONLINE'

OPEN c
WHILE 1 = 1
BEGIN
FETCH
NEXT FROM c INTO @db
IF @@FETCH_STATUS <> 0
    
BREAK
SET
@sql =
    
'SELECT
   @@SERVERNAME AS server_name
  ,'''
+ @db + ''' AS db_name
  ,CAST(DATABASEPROPERTYEX('''
+ @db + ''', ''Collation'') AS sysname) AS db_collation
  ,OBJECT_NAME(c.object_id, '
+ CAST(DB_ID(@db) AS sysname) + ') AS table_name
  ,c.name AS column_name
  ,c.collation_name AS column_collation
FROM '
+ QUOTENAME(@db) + '.sys.columns AS c
  INNER JOIN '
+ QUOTENAME(@db) + '.sys.tables AS t ON t.object_id = c.object_id
WHERE t.type = ''U''
  AND c.collation_name IS NOT NULL
  AND c.collation_name <> CAST(DATABASEPROPERTYEX('''
+ @db + ''', ''Collation'') AS sysname)
'
--PRINT @sql
INSERT INTO #res
EXEC(@sql)
END
CLOSE
c
DEALLOCATE c
SELECT * FROM #res
Published Wednesday, May 23, 2012 7:58 PM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Thank you Tibor, very useful script.Usually, in the past I got SQLDMO object library to script out objects without collation (Const SQLDMOScript2_NoCollation As Long = 8388608) so running on the dest. server will defaut database collation

May 28, 2012 1:32 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement