Results 1 to 2 of 2

Thread: Alter Columns?

  1. #1
    Join Date
    Apr 2004
    Location
    San Antonio, TX
    Posts
    1

    Angry Unanswered: Alter Columns?

    Hello,

    I am trying to edit several tables that were imported in tab-delimited format from text files. I am trying to generate a script that will alter the data type for several different columns.

    I have succesfully edited a single column with the following code:
    USE THCIC
    ALTER TABLE PudfTest
    ALTER COLUMN
    DISCHARGE VARCHAR(6) NULL

    However, I have need to create a script that will change the data type for over 100 columns. So far, everything I've read tells me that multiple 'alter column' statements cannot be run in a single query. I'm hoping someone can shed some light on this, or at least point me in another direction so that I won't have to manually change the data type for every column in each of the tables.

    Any help would be greatly appreciated.
    Thanks!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Look into this one and elaborate as needed:

    Code:
    select 'alter table ' + table_name + ' alter column ' + column_name + ' ' +
    case data_type
       when  'int' then 'varchar(25)' 
       when 'datetime' then 'char(10)' 
       else data_type 
    end + ' null'
    from information_schema.columns

Posting Permissions

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