Script/tool to update schema of my database when the latest_schema script is provided
-
Hi All, I want to write a script/tool which would update the schema of the existing database (in SQL Server 2005) i.e Add/drop a table, column, constraint etc. on a ongoing basis. The input to my tool would be the latest_schema script which creates the database with all the latest tables, columns, constraints etc. So the tool that I am going to write should perform a schema-diff with my db Vs the script provided and add/drop the tables, cols, constraints on my database. I have a couple of solutions in my mind but feel that they are not very efficient. 1. Create a new database running the provided script, write a stored procedure to loop thro all the tables to figure out the diff between the existing db and the current db and update the current db: Problem here is my current db has data and lots of constraints, which might pose a problem. 2. Use some third party tools to generate the sync script and execute it against my current db but I am not sure about the free tools available out there. Can somebody please provide me some guidelines/tips on how to go ahead about this. Thanks in advance. PJ