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:
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.
2019-10-03
