Erik Does SQL Server Devops

Posted on by Erik Darling

No, Not Really

But I did write a silly helper procedure for myself, that I figured I’d share here. I’m not putting it on GitHub right now (unless you all find it incredibly useful).

I often have multiple copies of StackOverflow for demos. Smaller databases, ones in different compat levels, and other weird tweaks to test things (this is easier and less confusing than remembering to reset everything, and freaking out when old demos stop working because I forgot).

What I always got annoyed with was going to try something in a different database, and remembering that a proc, function, or view I created wasn’t there, or had changed.

With that, I give you sp_MoveGuts.

A simple call looks like this:

EXEC dbo.sp_MoveGuts @SourceDatabase = N'StackOverflow2013', --where to take stuff
                     @TargetDatabase = N'StackOverflow', --where to make stuff
                     @CreateOnly = 0, --Will alter objects if they already exist
                     @Debug = 1 --optional;

You need to give it a source database, a target database, and tell it whether you want to only create new objects, or also alter any existing objects.

There are certain things I’m not going to spend time trying to fix, like dependencies. If you have code that references tables that don’t exist in other databases, errors will be logged and you’ll be notified. But I’m not going to move physical data in this thing. No way, no how. That part is up to you.

Anyway, here it is.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

The Code

CREATE OR ALTER PROCEDURE dbo.sp_MoveGuts
  @SourceDatabase sysname = N'', 
  @TargetDatabase sysname = N'',
  @CreateOnly BIT = 0,
  @Debug BIT = 0
  AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
 
DECLARE @get_sql NVARCHAR(MAX) = N'';

CREATE TABLE #mover
(
    id INT IDENTITY PRIMARY KEY CLUSTERED,
    object_id BIGINT,
    target_database sysname,
    object_name sysname,
    definition NVARCHAR(MAX),
    alter_definition 
           AS STUFF(definition, CHARINDEX(N'CREATE', definition), 6, N'ALTER')
);

DECLARE @logger TABLE
(
    id INT IDENTITY PRIMARY KEY CLUSTERED, 
    object_name sysname, 
    error_number INT, 
    error_message NVARCHAR(2048)
);


SET @get_sql += N'
SELECT asm.object_id,
       @iTargetDatabase,
       ISNULL(OBJECT_NAME(asm.object_id, DB_ID(@iSourceDatabase)), ''a_trigger'') AS object_name,
       LTRIM(RTRIM(asm.definition)) as definition
FROM ' + QUOTENAME(@SourceDatabase) + N'.sys.sql_modules AS asm;
' + CHAR(13);

IF @Debug = 1 BEGIN RAISERROR(@get_sql, 0, 1) WITH NOWAIT; END;

INSERT #mover ( object_id, target_database, object_name, definition )
EXEC sys.sp_executesql @get_sql, N'@iTargetDatabase sysname, @iSourceDatabase sysname', @TargetDatabase, @SourceDatabase;


IF @Debug = 1 BEGIN SELECT * FROM #mover AS m; END;

DECLARE @min_id INT;
DECLARE @max_id INT;
DECLARE @spe NVARCHAR(MAX) = QUOTENAME(@TargetDatabase) + N'.sys.sp_executesql ';
DECLARE @def_sql NVARCHAR(MAX) = N'';
DECLARE @def_sql_alt NVARCHAR(MAX) = N'';
DECLARE @object_name sysname = N''

SELECT @min_id = MIN(m.id), 
       @max_id = MAX(m.id)
FROM #mover AS m
OPTION(RECOMPILE);

    WHILE @min_id <= @max_id
    BEGIN
    BEGIN TRY 
    
        SELECT @object_name = m.object_name,
               @def_sql = m.definition,
               @def_sql_alt = m.alter_definition
        FROM #mover AS m
        WHERE m.id = @min_id
        OPTION (RECOMPILE);
           
        IF @Debug = 1 BEGIN RAISERROR(N'creating %s using %s', 0, 1, @object_name, @spe) WITH NOWAIT; END;        
        EXEC @spe @def_sql;

        IF @Debug = 1 BEGIN RAISERROR(N'Setting next id after %i out of %i total', 0, 1, @min_id, @max_id) WITH NOWAIT; END;
        
        SET @min_id = 
        (
            SELECT TOP (1) m.id
            FROM #mover AS m
            WHERE m.id > @min_id
            ORDER BY m.id
        );

        IF (@min_id IS NULL 
              OR @min_id = @max_id)
        BREAK;
     
     END TRY

     BEGIN CATCH
     IF XACT_STATE() <> 0 
         ROLLBACK TRANSACTION;

         IF (ERROR_NUMBER() = 2714
                AND @CreateOnly = 0 )
             BEGIN

                 IF @Debug = 1 BEGIN RAISERROR(N'Object exists, altering %s using %s instead', 0, 1, @object_name, @spe) WITH NOWAIT; END;        
                 EXEC @spe @def_sql_alt;

                IF @Debug = 1 BEGIN RAISERROR(N'Setting next id after %i out of %i total', 0, 1, @min_id, @max_id) WITH NOWAIT; END;
                
                SET @min_id = 
                (
                    SELECT TOP (1) m.id
                    FROM #mover AS m
                    WHERE m.id > @min_id
                    ORDER BY m.id
                );
                
                IF (@min_id IS NULL 
                      OR @min_id = @max_id)
                BREAK;
                
             END
         ELSE 
         BEGIN
             INSERT @logger 
                  ( object_name, error_number, error_message )
             SELECT @object_name, ERROR_NUMBER(), ERROR_MESSAGE();

             SET @min_id = 
             (
                 SELECT TOP (1) m.id
                 FROM #mover AS m
                 WHERE m.id > @min_id
                 ORDER BY m.id
             );
             
             IF (@min_id IS NULL 
                   OR @min_id = @max_id)
             BREAK;
         END

     END CATCH
END;
    
    IF EXISTS (SELECT 1/0 FROM @logger AS l)
        BEGIN
            SELECT N'errors' AS errors, * FROM @logger AS l ORDER BY l.id;
        END

END;
GO

 

Related Posts