Post reply

change data type in all the tables

  • June 15, 2010 at 6:15 am

    #387157

    hi

    i have 47 tables in my database.

    now i want data nchar(20) to nchar(200) in all the tables

    should i do it manually?

    please help

  • June 15, 2010 at 6:58 am

    #1180366

    This might not necessarily be the easiest or most correct, but my own thoughts...

    script out the "Drop and create" whole database structure, but before creating, change the data type!

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • June 15, 2010 at 7:35 am

    #1180404

    i think you can generate the ALTER TABLE ALTER COLUMN statements with this:

    select

    schema_name(objz.schema_id) as schemaname,

    objz.name as tablename,

    colz.name as columnname,

    colz.max_length,

    'ALTER TABLE ' + schema_name(objz.schema_id) + '.'

    + objz.name + 'ALTER COLUMN ' + colz.name + ' '

    + type_name(colz.system_type_id) + '(256)'

    from sys.columns colz

    inner join sys.tables objz on colz.object_id = objz.object_id

    where is_ms_shipped = 0 --don't fiddle with ms stuff

    and type_name(colz.system_type_id) = 'nvarchar' --right data type?

    and colz.max_length = 20 --right size?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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