Post reply

Globally change Nvarchar to Varchar

  • October 22, 2005 at 2:20 am

    #89801

    Hi

    I designed a database with hundreds of Nvarchar fields in many tables, The data I am storing does not and will not require nvarchar.

    Does anyone know a way of globally changing the Nvarchar columns to Varchar - keeping the same defined size.

    Many thanks

    David

  • October 22, 2005 at 7:33 am

    #599354

    David - the only way I can think of is by directly updating the syscolumns table - for that you would first have to check the box that says Allow modifications to be made directly to the system catalogs in the sql server properties box...

    connect to your database and then run a query similar to this: Please do a select first to make sure you're changing only the tables that you want!

    update syscolumns
set xusertype = 167
where name in
(select sc.name from syscolumns sc
inner join sysobjects so
on sc.id = so.id
where so.xtype = 'u'
and sc.xusertype = 231
and so.name in ('tbl1', 'tbl2', 'tbl3'))

    Don't forget to go back and uncheck the box that allows modifications!







    **ASCII stupid question, get a stupid ANSI !!!**

  • October 22, 2005 at 8:58 am

    #599358

    It worked! and has saved me hours of work on over a thousand columns across dozens of tables.

    Many Thanks!

  • October 22, 2005 at 9:26 am

    #599360

    Glad to have been of help!







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply