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:

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;

USE tempdb;

-- 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.

Leave a Reply Cancel reply