locked
How do I drop a column from a table? RRS feed

  • Question

  • Using VS2010 and a database project, I can refactor and rename a column, but I can see no means of deleting/dropping a column from a table. If I edit the CREATE TABLE statement and remove the column there, it may still have dependencies that need to be addressed.

    I think I am missing something obvious because I can find very little info on the subject and surely there are others out there who need to remove columns from tables using VS2010....

    Tuesday, September 14, 2010 7:12 PM

Answers

  • Dear Jackson,

    You can put the ALTER TABLE DROP COLUMN statement either in the pre-deployment or post-deployment script. The build step compiles your database project into the files that are used for deployment. These files include the .dbschema file, the .deploymentmainfest file, and the pre-deployment and post-deployment scripts. No comparison with a target database is performed when you build the database project. When you deploy the database project, the output of the build action is compared to the target database, if the target database exists, and the actions that are required to update the target to match the database project are identified. Depending on your settings, those updates are deployed to the target database.

    That’s true we currently may not able to delete a column from the VS GUI. I will contact the PG to verify whether it will be available in the furture release or not.

    Thanks and Best Regards,

    Vicky Song


    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Thursday, September 16, 2010 1:54 AM
    Moderator

All replies

  • Hello Jackson,

    For your issue, I have some explanations and suggestions hopeful it helps you.

    You can delete a column from a table by using the following script:

    ALTER TABLE MyTableName

    DROP COLUMN MyColumnName

    Or just as you say, you can also delete the definition of the column in the create table statement. When you delete a column which is referenced by other table you can’t delete it until the dependency not exists.

    Let me know if you have any further concerns.

    Vicky Song


    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Wednesday, September 15, 2010 9:47 AM
    Moderator
  • Thanks Vicky... Where would you suggest that I place my ALTER TABLE DROP COLUMN statement? If I place it in the pre-deployment script, how will the database project become aware of the change and update my schema accordingly?

    From what you are saying, it seems there is no way to drop a column via the VS2010 GUI... and I must address the dependencies manually. But perhaps deleting a column would be an option in the Refactor menu in an upcoming release?

     

    Wednesday, September 15, 2010 11:18 AM
  • Dear Jackson,

    You can put the ALTER TABLE DROP COLUMN statement either in the pre-deployment or post-deployment script. The build step compiles your database project into the files that are used for deployment. These files include the .dbschema file, the .deploymentmainfest file, and the pre-deployment and post-deployment scripts. No comparison with a target database is performed when you build the database project. When you deploy the database project, the output of the build action is compared to the target database, if the target database exists, and the actions that are required to update the target to match the database project are identified. Depending on your settings, those updates are deployed to the target database.

    That’s true we currently may not able to delete a column from the VS GUI. I will contact the PG to verify whether it will be available in the furture release or not.

    Thanks and Best Regards,

    Vicky Song


    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Thursday, September 16, 2010 1:54 AM
    Moderator