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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
USE tempdb; GO -- Copying from [GREGT580] to local server SET NOCOUNT ON; DECLARE @MissingProcedures TABLE ( MissingProcedureID int IDENTITY(1,1) PRIMARY KEY, SchemaName sysname, ProcedureName sysname, ProcedureDefinition nvarchar(max) ); INSERT @MissingProcedures ( SchemaName, ProcedureName, ProcedureDefinition ) SELECT s.[name], p.[name], sm.definition FROM [GREGT580].AdventureWorks.sys.procedures AS p INNER JOIN [GREGT580].AdventureWorks.sys.schemas AS s ON p.schema_id = s.schema_id INNER JOIN [GREGT580].AdventureWorks.sys.sql_modules AS sm ON sm.object_id = p.object_id WHERE NOT EXISTS (SELECT 1 FROM sys.procedures AS pl INNER JOIN sys.schemas AS sl ON sl.schema_id = pl.schema_id AND sl.[name] = s.[name] COLLATE DATABASE_DEFAULT AND pl.[name] = p.[name] COLLATE DATABASE_DEFAULT); DECLARE @SchemaName sysname; DECLARE @ProcedureName sysname; DECLARE @ProcedureDefinition nvarchar(max); DECLARE @Counter int = 1; WHILE @Counter < (SELECT MAX(MissingProcedureID) FROM @MissingProcedures AS mp) BEGIN SELECT @SchemaName = mp.SchemaName, @ProcedureName = mp.ProcedureName, @ProcedureDefinition = mp.ProcedureDefinition FROM @MissingProcedures AS mp WHERE mp.MissingProcedureID = @Counter; EXEC SDU_Tools.ExecuteOrPrint @ProcedureDefinition; -- Change to EXEC (@ProcedureDefinition) to create SET @Counter += 1; END; |
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.