¿Cómo poder cambiar el collate en toda una BBDD de SQL?

gabrielflowers · #4 03-03-2015

Por si a alguien le sirve la forma correcta de cambiar la intercalacion de una base de datos, de todas sus tablas, y campos seria:

Código SQL [-]
ALTER DATABASE MiBd --bd 
COLLATE Modern_Spanish_CI_AS;  --intercalacion

--

use MiBD--bd
declare @var1 as nvarchar(max)

DECLARE micursor CURSOR SCROLL FOR
SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN [' + C.name + '] ' + 
T.name +'(' + CAST(c.length as varchar(4)) + ') COLLATE ' + 
'Modern_Spanish_CI_AS; ' --poner aca la intercalacion
from syscolumns C, sysobjects O, systypes T 
where (C.collation is not null) and C.id = O.id and c.TYPE = T.TYPE AND
C.XUSERTYPE = T.XUSERTYPE AND o.type = 'U'  
ORDER BY O.NAME;

OPEN micursor
FETCH NEXT FROM micursor INTO @var1
WHILE(@@FETCH_STATUS = 0 ) 
BEGIN
  --codigo transact sql
  --select @var1
  exec(@var1)
  
  FETCH NEXT FROM micursor INTO @var1
END
CLOSE micursor
DEALLOCATE micursor