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
 

wef said:

http://www.chanel-outlet.co    Chanel Outlet

http://www.payless-shoes.co    payless shoes

http://www.80s-fashion.org    80's Fashion

http://www.3chathk.com    Nike Air Jordan Enfants

http://www.michaelkorsoutlet-vip.us    Michael Kors Outlet

http://www.ybreen.com    Air Jordan Retro 3

http://www.raybanshop.us    ray ban shop

http://www.promise--rings.us    Promise Rings - Official

http://www.tuxedos4u.com    populaire Nike Lunarmax

http://www.puma-outlet.net    puma outlet

http://www.coachfactoryoutlet.cc    coach factory outlet

http://www.katespade-outlet.net    katespade outlet

http://www.abirdtoldme.fr    Nike Air Force

http://www.coachbagsfactoryoutlet.us    Coach Bags Outlet

http://www.nikeschuheshop.de    NIKE Schuhe Shop

http://www.casio-watches.us    casio watches

http://www.michelewatch.us    michele watch

http://www.oakley-australia.net    oakley australia

http://www.ad29.fr    Air Jordan 10

http://www.qiqifashion.us/    qiqi fashion

http://www.birkenstocksandals.co    birkenstock sandals

http://www.burberrybagsoutlet.us    burberry bags outlet

http://www.michael-kors-shoes.us    michael kors shoes

http://www.menssunglasses.us    Sunglasses outlet sale

http://www.searchedtabsonline.com    Nike Air Jordan Enfants

http://www.raybanclubmaster.us    ray ban clubmaster sunglasses

http://www.cartier-watches.org    Cartier Watches

http://www.pumaoutlet.net    puma outlet online

http://www.hermesbag.us    hermes bag

http://www.louisvuittonoutletbox.com    louis vuitton outlet

http://www.coach-factory-outlet.org/    coach factory outlet

http://www.clearanceoutlet.us/    clearance

http://www.prada-outlet.us.com    prada outlet woodbury

meadc2.11

February 10, 2018 7:23 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement