SQL: Create missing stored procedures using a linked server

I answered an interesting question the other day on Stack Overflow. What the person who posted was after was a way to create all missing stored procedures on one database that were present on another database on another server.

Here's an example of how to do that:

I start by querying sys.procedures, sys.schemas, and sys.sql_modules on the remote database to find the ones that aren't present on the local system. I needed sys.sql_modules to get the definition of the procedure.

This solution uses a linked server as requested by the person posting. Just change the [GREGT580] to whatever your server name is. If you are on Azure SQL DB, a similar outcome could be created using external tables. I've provided details on how to do that here.

Then I just looped around creating the procedures.

Note that I used SDU_Tools.ExecuteOrPrint to print out the definitions. You could use just PRINT but it barfs on long strings. (ExecuteOrPrint is just one of the useful tools you'll find in SDU_Tools here).

To actually create, them, change the line that prints them out to just say EXEC(@ProcedureDefinition) instead.

Hope that helps someone else apart from the person who posted.

Leave a Reply

Your email address will not be published. Required fields are marked *