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