locked
SQL 2008, alter a column which is mostly used in a database RRS feed

  • Question

  • Hi,

    In my SQL Server 2008, I would like to alter a column, my goal is to change that column from char(10) to char(11).
    That column is used in many tables in a database, it is also a foreign key of many other tables, and it is also in cluster index in many tables.
    How do I do that?
    Thanks for help.

    Jason

    Monday, January 9, 2017 2:16 AM

Answers

  • Hi Jason,

    I am afraid there is no In place ALTER option here if this column is part of Clustered Index or Foreign Key. You have to break the Table relationship, Alter the column length using ALTER TABLE tablename ALTER COLUMN colname  command and setup table relationship again. You have to do this per Table where this column is.

    For Tables where this column is in Clustered Index, you have to drop clustered index, Alter column length and re-create it.

    Good this is, you can easily script out those relationship and clustered index definition and reuse them.

    Let me know if need more help.

    Thanks,


    Kindly mark the reply as answer if they help

    Monday, January 9, 2017 6:03 AM
  • Hi,

    As this table is used in relations you need to break this relations and change the column and then go to reference tables and change it to new char(11) and implement the relation again.

    I recommend is to do it in the training environment and then apply the script to live environment. 

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    • Marked as answer by JasonHuang8888 Tuesday, January 10, 2017 12:50 AM
    Monday, January 9, 2017 6:14 AM

All replies

  • Hi Jason,

    I am afraid there is no In place ALTER option here if this column is part of Clustered Index or Foreign Key. You have to break the Table relationship, Alter the column length using ALTER TABLE tablename ALTER COLUMN colname  command and setup table relationship again. You have to do this per Table where this column is.

    For Tables where this column is in Clustered Index, you have to drop clustered index, Alter column length and re-create it.

    Good this is, you can easily script out those relationship and clustered index definition and reuse them.

    Let me know if need more help.

    Thanks,


    Kindly mark the reply as answer if they help

    Monday, January 9, 2017 6:03 AM
  • Hi,

    As this table is used in relations you need to break this relations and change the column and then go to reference tables and change it to new char(11) and implement the relation again.

    I recommend is to do it in the training environment and then apply the script to live environment. 

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    • Marked as answer by JasonHuang8888 Tuesday, January 10, 2017 12:50 AM
    Monday, January 9, 2017 6:14 AM