As dori si eu sa schimb COLLATION-ul la toate tabelele USER dintr-o anumita baza de date

Am incercat cu ALTER DATABASE mydatabase COLLATE xxxxx dar se pare ca aceasta schimba doar collation-ul la tabelele SYS

Varianta gasita de mine ar  fi sa folosesc un cursor pe tabela sysobjects de unde sa incarc toate tabele si coloanele aferenta si sa folosesc comanda ALTER TABLE mytable ALTER COLUMN column1 COLLATE xxx

Exista si o alta comanda mai rapida?

Folosesc SQLServer 2000

 

Multumesc  

 

 

 

Puteti folosi urmatorul script:

DECLARE @myCollation NVARCHAR(4000);
SET @myCollation = N'SQL_Latin1_General_CP1_CI_AS'
SELECT
 'ALTER TABLE [' + SO.name + '] ' +
 'ALTER COLUMN [' + SC.name + '] ' +
 ST.name + '(' + cast(SC.length as varchar) + ') ' +
 'COLLATE ' + @myCollation -- SC.collation -- Original collation
 + ' ' +
 CASE WHEN SC.isnullable = 1 THEN 'NULL' ELSE 'NOT NULL' END SqlStatement
 FROM syscolumns SC
 JOIN sysobjects SO ON SO.id = SC.id
 JOIN systypes ST ON ST.xtype = SC.xtype
 WHERE SC.collation IS NOT NULL

Ca rezultat partial pe Northwind:

ALTER TABLE [Alphabetical list of products] ALTER COLUMN [ProductName] nvarchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [Alphabetical list of products] ALTER COLUMN [ProductName] sysname(80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [Alphabetical list of products] ALTER COLUMN [QuantityPerUnit] nvarchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
...

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro

Este ok scriptul 

Cred ca mai trebuie adaugat si : and SO.xtype='U' in clauza where pt a lua numai tabelele USER

deci in final arata cam asa :

DECLARE @myCollation NVARCHAR(4000);
SET @myCollation = N'Latin1_General_BIN'
SELECT
 'ALTER TABLE [' + SO.name + '] ' +
 'ALTER COLUMN [' + SC.name + '] ' +
 ST.name + '(' + cast(SC.length as varchar) + ') ' +
 'COLLATE ' + @myCollation -- SC.collation -- Original collation
 + ' ' +
 CASE WHEN SC.isnullable = 1 THEN 'NULL' ELSE 'NOT NULL' END SqlStatement
 FROM syscolumns SC
 JOIN sysobjects SO ON SO.id = SC.id
 JOIN systypes ST ON ST.xtype = SC.xtype
 WHERE SC.collation IS NOT NULL
 and SO.xtype='U'

Multam mult 

Mihai