locked
Alter columns data type of tables RRS feed

  • Question

  • Hi All,

    I want to change the datatype of column of tables whichever has datatype 'char'. I want to change it to varchar using tsql script in one shot. I would highly appreciate any help in this regard.

    Thursday, February 28, 2013 5:42 PM

Answers

  • In addition to what they have said, there is one really large spanner in the works. You can't just change the type of a column if it is used in an index, constraint, computed column, etc.   So say you have the following table (I threw in a nchar to the script to make it more useful to more people:

    drop table changeType
    go
    create table changeType
    (
     value  char(10) primary key,
     value2 char(15) unique,
     value3 char(20),
     value4 as (value3),
     value5 nchar(40)
    )
    go
    create index value5 on changeType(value5)
    go

    Running the following query will build you the script. I didn't use information schema because it has some issues with schemas, and if you use schemas other than the default you need to include it:

    SELECT    'alter table ' + schemas.name collate database_default + '.' + tables.name collate database_default + ' ' +
            ' alter column ' + columns.name + ' ' +
               CASE WHEN types.name = 'char' then 'varchar(' + CAST(columns.max_length AS VARCHAR(4))  + ')'
            when types.name = 'nchar' then 'nvarchar(' + CAST(columns.max_length / 2 AS VARCHAR(4)) + ')'
                        END  + char(13) + char(10) + 'GO' as Statements
              FROM      sys.columns
                        JOIN sys.types
                            ON columns.user_type_id = types.user_type_id
                        JOIN sys.types AS baseType
                            ON columns.system_type_id = baseType.system_type_id
                               AND baseType.user_type_id = baseType.system_type_id
                        JOIN sys.tables
          JOIN sys.schemas
                            ON schemas.schema_id = tables.schema_id
                            ON tables.object_id = columns.OBJECT_ID
    WHERE types.name in ('nchar', 'char')
     and  tables.name = 'changeType'
     go

    The output of that query is the following script:

    alter table dbo.changeType  alter column value varchar(10)
    GO
    alter table dbo.changeType  alter column value2 varchar(15)
    GO
    alter table dbo.changeType  alter column value3 varchar(20)
    GO
    alter table dbo.changeType  alter column value4 varchar(20)
    GO
    alter table dbo.changeType  alter column value5 nvarchar(40)
    GO

    But every one of the changes will fail due to the data being used in a constraint/index:

    Msg 5074, Level 16, State 1, Line 2
    The object 'PK__changeTy__40BBEA3B0A03BCCB' is dependent on column 'value'.
    Msg 4922, Level 16, State 9, Line 2
    ALTER TABLE ALTER COLUMN value failed because one or more objects access this column.
    Msg 5074, Level 16, State 1, Line 1
    The object 'UQ__changeTy__4645F83AF1DD27BC' is dependent on column 'value2'.
    Msg 4922, Level 16, State 9, Line 1
    ALTER TABLE ALTER COLUMN value2 failed because one or more objects access this column.
    Msg 5074, Level 16, State 1, Line 1
    The column 'value4' is dependent on column 'value3'.
    Msg 4922, Level 16, State 9, Line 1
    ALTER TABLE ALTER COLUMN value3 failed because one or more objects access this column.
    Msg 4928, Level 16, State 1, Line 1
    Cannot alter column 'value4' because it is 'COMPUTED'.
    Msg 5074, Level 16, State 1, Line 1
    The index 'value5' is dependent on column 'value5'.
    Msg 4922, Level 16, State 9, Line 1
    ALTER TABLE ALTER COLUMN value5 failed because one or more objects access this column.

    What I do in these cases is use a tool like Red-gate compare, (I think SS Data tools will work too, and it is free http://msdn.microsoft.com/en-us/data/gg427686) make an (at least) schema only copy of the database (they have a snapshot feature that works, or just make a duplicate of your database), then drop all of the offending constraints until everything works as hoped and the columns are all changed.  Then use the compare to find the contraints you removed and put them back on.

    Using that approach with an empty copy of the database to change the types can be faster (particularly if you have a dev copy that matches prod structurally), then use the compare tools to apply the changes to production (and the tools do a great job of failing if something goes wrong, and particularly adding constraints and stuff back in the process).


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Kalman Toth Thursday, February 28, 2013 11:12 PM
    • Marked as answer by Maggie Luo Thursday, March 7, 2013 9:07 AM
    Thursday, February 28, 2013 10:01 PM

All replies

  • Try -

    SELECT 'ALTER TABLE '+ TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' VARCHAR(10)'
    FROM 
    INFORMATION_SCHEMA.COLUMNS
    WHERE DATA_TYPE LIKE 'CHAR'

    Alter the length of VARCHAR accordingly.

    Copy the output and execute.

    I would recommend to test this on your local server first.

    Edit : Sample output with AdventureWorks database -

    ALTER TABLE StudentDetails ALTER COLUMN StudentName VARCHAR(10)
    ALTER TABLE Customer ALTER COLUMN AccountNumber VARCHAR(10)
    ALTER TABLE SalesOrderHeader ALTER COLUMN CreditCardApprovalCode VARCHAR(10)
    ALTER TABLE Password ALTER COLUMN PasswordHash VARCHAR(10)
    ALTER TABLE Password ALTER COLUMN PasswordSalt VARCHAR(10)


    Narsimha


    • Edited by Naarasimha Thursday, February 28, 2013 6:21 PM
    Thursday, February 28, 2013 6:19 PM
  • Here is a related thread:

    http://stackoverflow.com/questions/179987/sql-server-script-to-update-database-columns-from-varchar-to-nvarchar-if-not-a

    Quote from the thread: "Thank you for useful query, however it doesn't work in case if there are depending objects like foreign keys or stored procedures"

    SS ALTER TABLE:

    http://msdn.microsoft.com/en-us/library/ms190273.aspx


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Thursday, February 28, 2013 6:51 PM
  • In addition to what they have said, there is one really large spanner in the works. You can't just change the type of a column if it is used in an index, constraint, computed column, etc.   So say you have the following table (I threw in a nchar to the script to make it more useful to more people:

    drop table changeType
    go
    create table changeType
    (
     value  char(10) primary key,
     value2 char(15) unique,
     value3 char(20),
     value4 as (value3),
     value5 nchar(40)
    )
    go
    create index value5 on changeType(value5)
    go

    Running the following query will build you the script. I didn't use information schema because it has some issues with schemas, and if you use schemas other than the default you need to include it:

    SELECT    'alter table ' + schemas.name collate database_default + '.' + tables.name collate database_default + ' ' +
            ' alter column ' + columns.name + ' ' +
               CASE WHEN types.name = 'char' then 'varchar(' + CAST(columns.max_length AS VARCHAR(4))  + ')'
            when types.name = 'nchar' then 'nvarchar(' + CAST(columns.max_length / 2 AS VARCHAR(4)) + ')'
                        END  + char(13) + char(10) + 'GO' as Statements
              FROM      sys.columns
                        JOIN sys.types
                            ON columns.user_type_id = types.user_type_id
                        JOIN sys.types AS baseType
                            ON columns.system_type_id = baseType.system_type_id
                               AND baseType.user_type_id = baseType.system_type_id
                        JOIN sys.tables
          JOIN sys.schemas
                            ON schemas.schema_id = tables.schema_id
                            ON tables.object_id = columns.OBJECT_ID
    WHERE types.name in ('nchar', 'char')
     and  tables.name = 'changeType'
     go

    The output of that query is the following script:

    alter table dbo.changeType  alter column value varchar(10)
    GO
    alter table dbo.changeType  alter column value2 varchar(15)
    GO
    alter table dbo.changeType  alter column value3 varchar(20)
    GO
    alter table dbo.changeType  alter column value4 varchar(20)
    GO
    alter table dbo.changeType  alter column value5 nvarchar(40)
    GO

    But every one of the changes will fail due to the data being used in a constraint/index:

    Msg 5074, Level 16, State 1, Line 2
    The object 'PK__changeTy__40BBEA3B0A03BCCB' is dependent on column 'value'.
    Msg 4922, Level 16, State 9, Line 2
    ALTER TABLE ALTER COLUMN value failed because one or more objects access this column.
    Msg 5074, Level 16, State 1, Line 1
    The object 'UQ__changeTy__4645F83AF1DD27BC' is dependent on column 'value2'.
    Msg 4922, Level 16, State 9, Line 1
    ALTER TABLE ALTER COLUMN value2 failed because one or more objects access this column.
    Msg 5074, Level 16, State 1, Line 1
    The column 'value4' is dependent on column 'value3'.
    Msg 4922, Level 16, State 9, Line 1
    ALTER TABLE ALTER COLUMN value3 failed because one or more objects access this column.
    Msg 4928, Level 16, State 1, Line 1
    Cannot alter column 'value4' because it is 'COMPUTED'.
    Msg 5074, Level 16, State 1, Line 1
    The index 'value5' is dependent on column 'value5'.
    Msg 4922, Level 16, State 9, Line 1
    ALTER TABLE ALTER COLUMN value5 failed because one or more objects access this column.

    What I do in these cases is use a tool like Red-gate compare, (I think SS Data tools will work too, and it is free http://msdn.microsoft.com/en-us/data/gg427686) make an (at least) schema only copy of the database (they have a snapshot feature that works, or just make a duplicate of your database), then drop all of the offending constraints until everything works as hoped and the columns are all changed.  Then use the compare to find the contraints you removed and put them back on.

    Using that approach with an empty copy of the database to change the types can be faster (particularly if you have a dev copy that matches prod structurally), then use the compare tools to apply the changes to production (and the tools do a great job of failing if something goes wrong, and particularly adding constraints and stuff back in the process).


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Kalman Toth Thursday, February 28, 2013 11:12 PM
    • Marked as answer by Maggie Luo Thursday, March 7, 2013 9:07 AM
    Thursday, February 28, 2013 10:01 PM
  • Thanks Louis for the explanation..!

    Regards Chenchi MSSQL Server DBA

    Friday, March 1, 2013 5:34 PM