change collation for each table in database

Question

Hi all,

how do I change collation for each table in database (using one command if possible) ? 

Answer 1

Does this help? http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e77cdd31-d6bd-46a0-afe4-77767cefd821/change-collation-per-table-level?forum=transactsql

---

Thanks, Andrew
My blog...

Answer 2

The collation is defined on column level, not on a table

See TechNet ScriptCenter: Column Collation Changer

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

use INFORMATION_SCHEMA.COLUMNS table and look for columns with different collation. then generate code using below query

SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '('+ CHARACTER_MAXIMUM_LENGTH + ') ' + CASE WHEN ID_NULLABLE ='YES' THEN ' NULL ' ELSE 'NOT NULL COLLATE <your collation name here>;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar','char','nvarchar')

then copy result onto a new window and click execute

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs