Intrebari

Re: COLLATION change

urechemm — Wed, 01 Nov 2006 11:09:33 GMT

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

Re: COLLATION change

xmldeveloper — Wed, 01 Nov 2006 10:57:18 GMT

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
...

COLLATION change

urechemm — Wed, 01 Nov 2006 10:55:53 GMT

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