Hej

Går det skriva ett script som ändrar collation på alla kolumner i alla tabeller i en databas?
Eller är det bara bita ihop och sätta igång för hand?

Hur påverkas index av att man gör detta?

mvh
Claes Andskär

Hej!
Hittade det här (otestat) på MSDN forumet för Transact SQL.
Pröva på en kopia (skapa en av en backup, t.ex) först.
<code>
DECLARE @new_collation varchar(128)

, @cmd_holder varchar(2000)

, @cmd_complete varchar(2000)

, @schema varchar(128)

, @table_name varchar(128)

, @column_name varchar(128)

, @data_type varchar(128)

, @length varchar(4)

, @nullability varchar(8)

SET @new_collation = 'latin1_general_cs_as'

SET @cmd_holder = '

ALTER TABLE $schema.$table_name

ALTER COLUMN $column_name $data_type($length) COLLATE $new_collation $nullability

'

DECLARE column_cursor CURSOR FOR

SELECT table_schema

, table_name

, column_name

, data_type

, CASE WHEN character_maximum_length = -1 THEN 'max' ELSE Convert(varchar(4), character_maximum_length) END As length

, CASE WHEN is_nullable = 'YES' THEN 'NULL' ELSE 'NOT NULL' END As nullability

FROM information_schema.columns

WHERE data_type IN ('char', 'varchar')

OPEN column_cursor

FETCH NEXT FROM column_cursor

INTO @schema, @table_name, @column_name, @data_type, @length, @nullability

WHILE @@Fetch_Status = 0

BEGIN

SELECT @cmd_complete = @cmd_holder

, @cmd_complete = Replace(@cmd_complete, '$schema' , @schema)

, @cmd_complete = Replace(@cmd_complete, '$table_name' , @table_name)

, @cmd_complete = Replace(@cmd_complete, '$column_name' , @column_name)

, @cmd_complete = Replace(@cmd_complete, '$data_type' , @data_type)

, @cmd_complete = Replace(@cmd_complete, '$length' , @length)

, @cmd_complete = Replace(@cmd_complete, '$new_collation', @new_collation)

, @cmd_complete = Replace(@cmd_complete, '$nullability' , @nullability)

PRINT @cmd_complete

--EXEC @cmd

FETCH NEXT FROM column_cursor

INTO @schema, @table_name, @column_name, @data_type, @length, @nullability

END

CLOSE column_cursor

DEALLOCATE column_cursor
</code>