Professional Web Applications Themes

change collation using TSQL(SQL2K) - Microsoft SQL / MS SQL Server

Dear all, Does anyone know how to change collation using TSQL. I mean without using Enterprise Manager. I have a table with 50ish varchar or char fields, I need to change collation from SQL_Latin1_General_CP1_CI_AS to the default. but I prefer not to change it field by field. It's too time comsuming. Must be some quick way of doing it. any suggestion will be great. Thanks. Jas...

  1. #1

    Default change collation using TSQL(SQL2K)

    Dear all,

    Does anyone know how to change collation using TSQL. I
    mean without using Enterprise Manager.

    I have a table with 50ish varchar or char fields, I need
    to change collation from SQL_Latin1_General_CP1_CI_AS to
    the default. but I prefer not to change it field by field.
    It's too time comsuming. Must be some quick way of doing
    it.

    any suggestion will be great.

    Thanks.

    Jas
    Jas Guest

  2. #2

    Default Re: change collation using TSQL(SQL2K)

    Hi Jas,

    There is not really a quick way of doing it. To chance the collation of a
    column with ALTER TABLE you have to drop and recreate all the indices,
    constraints (including Primary keys and Foreign Keys), defaults and
    explicitly created statistics on that column. Not a party, and I don't
    think anyone has written a script to do all that yet.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Jas" <com> wrote in message
    news:3b5501c355c0$eb185cb0$gbl... 


    Jacco Guest

  3. #3

    Default Re: change collation using TSQL(SQL2K)

    Thanks Bart,

    I was just starting work on a script for this myself, and I will see if I
    can use it.
    At the moment I have 2 issues:
    1) The script uses the stored procedure sp_displayoaerrorinfo, which is not
    provided with it.
    2) The sp that generates the index create scripts generates about 5 (not
    joking) statements for each table to add a primary key (on an INT column).

    I'll keep you posted if I find more issues.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Bart Duncan [MSFT]" <microsoft.com> wrote in message
    news:phx.gbl... 
    rights. 
    213.123.253.50 
    >
    >
    >[/ref]


    Jacco Guest

Similar Threads

  1. UK collation sequence
    By voipfc in forum MySQL
    Replies: 5
    Last Post: December 21st, 05:10 AM
  2. Slow SQL2k JDBC from Linux
    By lnagy in forum Coldfusion Database Access
    Replies: 0
    Last Post: April 1st, 05:21 PM
  3. SQL2K Trigger problem
    By Joe Bertolini in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 2nd, 08:42 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139