How to find all columns with a different collation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to find all columns with a different collation

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2011-02-21 : 07:19:02

I have a db with 100's of tables and have often collation issues. How can I find all table fields that are using a different collation than the base collation?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-21 : 08:00:11

[code]
USE YourDB
GO
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE
-- Check against database collation
COLLATION_NAME <> DATABASEPROPERTYEX('YourDB', 'Collation')
-- Check against instance collation
-- COLLATION_NAME <> SERVERPROPERTY('Collation')
[/code]

If the instance collation is not the same as the DB collation, you should also ensure that character columns in temp tables/variables are declared with COLLATE DATABASE_DEFAULT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources