In my earlier post Compare Stored Procedures in 2 SQL Server databases, I wrote about how it is possible to select from INFORMATION_SCHEMA to compare stored procedures.
The issue I am dealing with here is that during the development af a new system, the database stored procedures are likely to change between each release to a server (test, staging or production). You have made all the changes in your development database, and you just need to copy the changes to the other database. My old way to do this was to script each SP whenever it changed and link a section in the delivery slip to the script, which was quite tedious.
What I ended up with using in one of my current projects is the following steps:
- Using SQL Server Management Studio, script all procedures from the source database as ALTER to the clipboard.
- Connect a new query window to the target database and paste the script.
- Run the script.
It will fail for all the SPs that you have added to the source database, which are missing in the target database, but also it will update all existing stored procedures to the latest version. - Replace all “ALTER PROC” with “CREATE PROC” in the script and run it again.
It will fail for all existing SPs in the target database, but more importantly, it will create all the SPs from the source database that are missing in the target database. - And Bob’s your uncle!
This, of course, should be easy to automate. Maybe I’ll come back with an application that does it in an upcoming post.
The routine above works for functions as well, and should/could also work with views, and maybe also tables.
