T-SQL: (Migration) Export the definition of Stored Procedures

Very often we need to bulk export database objects and move them to another server. Manually we do this by right click on the database –> Tasks –> Generate Scripts…

In this example I show how we can export Stored Procedures with T-SQL and SQLCMD. This model gives us the flexibility to automate the process:

SQL Agent Job
SQLCMD
T-SQL (Input) – Script Stored Procedures.sql
T-SQL (Output) – Exported Stored Procedures.sql

In this example we’ll export the definitions of all the Stored Procedures (SP) in WideWorldImporters database.

First create a database, where store the SP that prints T-SQL code with more than 8000 characters. I found this SP long time ago in Internet and I use it successfully in my daily programming practice.

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	 BEGIN
		ALTER DATABASE PL
			SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	 END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

-- Clean up
IF (OBJECT_ID('[dbo].[usp_LongSQLPrint]', 'P') IS NOT NULL)
	BEGIN DROP PROC [dbo].[usp_LongSQLPrint]; END
GO

-- Create Stored Procedure
CREATE PROCEDURE [dbo].[usp_LongSQLPrint] (@String VARCHAR(MAX))
AS
BEGIN	
	SET @String = RTRIM(@String);

	DECLARE
		@Cr CHAR(1)  = CHAR(13)
		, @Lf CHAR(1) = CHAR(10);

	DECLARE
		@Len INT = 4000
		, @Cr_Index INT
		, @Lf_Index INT
		, @CrLf_Index INT
		, @Has_Cr_And_Lf BIT
		, @Left NVARCHAR(MAX)
		, @Reverse NVARCHAR(MAX);

	WHILE(LEN(@String) > @Len)
	BEGIN
		 SET @Left = LEFT(@String, @Len);
		 SET @Reverse = REVERSE(@Left);
		 SET @Cr_Index = @Len - CHARINDEX(@Cr, @Reverse) + 1;
		 SET @Lf_Index = @Len - CHARINDEX(@lf, @Reverse) + 1;
		 SET @CrLf_Index = CASE WHEN @Cr_Index < @Lf_Index THEN @Cr_Index ELSE @Lf_Index END;
		 SET @Has_Cr_And_Lf = CASE WHEN @Cr_Index < @Len AND @Lf_Index < @Len THEN 1 ELSE 0 END;
		 PRINT LEFT(@String, @CrLf_Index - 1);
		 
		 SET @String = RIGHT(@String, LEN( @String) - @CrLf_Index - @Has_Cr_And_Lf);
	END

	PRINT @String;
END
GO

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

CREATE DATABASE PL;

USE PL;

-- Clean up

IF (OBJECT_ID('[dbo].[usp_LongSQLPrint]', 'P') IS NOT NULL)

BEGIN DROP PROC [dbo].[usp_LongSQLPrint]; END

-- Create Stored Procedure

CREATE PROCEDURE [dbo].[usp_LongSQLPrint] (@String VARCHAR(MAX))

BEGIN

SET @String = RTRIM(@String);

DECLARE

@Cr CHAR(1) = CHAR(13)

, @Lf CHAR(1) = CHAR(10);

DECLARE

@Len INT = 4000

, @Cr_Index INT

, @Lf_Index INT

, @CrLf_Index INT

, @Has_Cr_And_Lf BIT

, @Left NVARCHAR(MAX)

, @Reverse NVARCHAR(MAX);

WHILE(LEN(@String) > @Len)

BEGIN

SET @Left = LEFT(@String, @Len);

SET @Reverse = REVERSE(@Left);

SET @Cr_Index = @Len - CHARINDEX(@Cr, @Reverse) + 1;

SET @Lf_Index = @Len - CHARINDEX(@lf, @Reverse) + 1;

SET @CrLf_Index = CASE WHEN @Cr_Index < @Lf_Index THEN @Cr_Index ELSE @Lf_Index END;

SET @Has_Cr_And_Lf = CASE WHEN @Cr_Index < @Len AND @Lf_Index < @Len THEN 1 ELSE 0 END;

PRINT LEFT(@String, @CrLf_Index - 1);

SET @String = RIGHT(@String, LEN( @String) - @CrLf_Index - @Has_Cr_And_Lf);

END

PRINT @String;

END

Next we need to run in SSMS the SP that actually prints the SP definitions. At this point we define which SP definitions to export.

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
GO

DECLARE
	@ListStoredProcedures BIT = $(ListStoredProcedures)
	, @AddCleanUp BIT = $(AddCleanUp)
	, @AddGrantPermission BIT = $(AddGrantPermission)
	, @PermissionName SYSNAME = '$(PermissionName)';

DECLARE @StoredProcedures TABLE
	(
		[object_id] INT
		, SchemaName SYSNAME
		, [Name] SYSNAME
	);

INSERT @StoredProcedures ([object_id], SchemaName, [Name])
SELECT
	[object_id]
	, SCHEMA_NAME([schema_id]) AS SchemaName
	, [name] AS StoredProcedureName
FROM sys.procedures
--WHERE
--	SCHEMA_NAME([schema_id]) LIKE '%FilterValue1%'
--	AND [name] LIKE '%FilterValue2%';

DECLARE @DynamicSQL1 VARCHAR(MAX);

SET @DynamicSQL1 = 'USE [WideWorldImporters];
GO
'
IF (@ListStoredProcedures = 1)
BEGIN
	SET @DynamicSQL1 += '
/*
Stored Procedures
*****************
' +
(
	SELECT DISTINCT
	STUFF( 
		(
			SELECT CONCAT(SchemaName, '.',  [Name]) + CHAR(13)
			FROM @StoredPRocedures
			FOR XML PATH('')
				, ROOT('Root')
				, TYPE
		).value('/Root[1]', 'NVARCHAR(MAX)'), 1, 0, ''
	)
)

SET @DynamicSQL1 += '*/

';
END

DECLARE
	@C_ObjectID INT
	, @C_SchemaName SYSNAME
	, @C_ProcedureName SYSNAME;

DECLARE @Cursor CURSOR;
 
SET @Cursor = CURSOR FOR
	SELECT *
	FROM @StoredPRocedures;

OPEN @Cursor;
FETCH NEXT FROM @Cursor
INTO
	@C_ObjectID
	, @C_SchemaName
	, @C_ProcedureName;
 
WHILE @@FETCH_STATUS = 0
BEGIN

SET @DynamicSQL1 += '----------********** ' + QUOTENAME(@C_SchemaName) + '.' + QUOTENAME(@C_ProcedureName) + ' - Start **********----------';

IF(@AddCleanUp = 1)
BEGIN
	SET @DynamicSQL1 += '
-- Clean up
IF (OBJECT_ID(''' + @C_SchemaName + '.' + @C_ProcedureName + ''', ''P'') IS NOT NULL)
	BEGIN DROP PROC ' + QUOTENAME(@C_SchemaName) + '.' + QUOTENAME(@C_ProcedureName) + '; END
GO
';
END

SET @DynamicSQL1 += '
-- SP Definition
';

SELECT @DynamicSQL1 += OBJECT_DEFINITION([object_id])
FROM sys.procedures
WHERE [object_id] = @C_ObjectID;

SET @DynamicSQL1 += '
GO
';

IF (@AddGrantPermission = 1)
BEGIN
	SET @DynamicSQL1 += '
-- Permissions
GRANT EXECUTE ON ' + QUOTENAME(@C_SchemaName) + '.' + QUOTENAME(@C_ProcedureName) + '
	TO [' + @PermissionName + '];
GO
';
SET @DynamicSQL1 += '----------********** ' + QUOTENAME(@C_SchemaName) + '.' + QUOTENAME(@C_ProcedureName) + ' - End **********----------

';
END

FETCH NEXT FROM @Cursor
INTO
	@C_ObjectID
	, @C_SchemaName
	, @C_ProcedureName;

END

CLOSE @Cursor;
DEALLOCATE @Cursor;

EXEC PL.dbo.usp_LongSQLPrint @DynamicSQL1;
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

SET NOCOUNT ON;

SET QUOTED_IDENTIFIER ON;

DECLARE

@ListStoredProcedures BIT = $(ListStoredProcedures)

, @AddCleanUp BIT = $(AddCleanUp)

, @AddGrantPermission BIT = $(AddGrantPermission)

, @PermissionName SYSNAME = '$(PermissionName)';

DECLARE @StoredProcedures TABLE

(

[object_id] INT

, SchemaName SYSNAME

, [Name] SYSNAME

);

INSERT @StoredProcedures ([object_id], SchemaName, [Name])

SELECT

[object_id]

, SCHEMA_NAME([schema_id]) AS SchemaName

, [name] AS StoredProcedureName

FROM sys.procedures

--WHERE

-- SCHEMA_NAME([schema_id]) LIKE '%FilterValue1%'

-- AND [name] LIKE '%FilterValue2%';

DECLARE @DynamicSQL1 VARCHAR(MAX);

SET @DynamicSQL1 = 'USE [WideWorldImporters];

IF (@ListStoredProcedures = 1)

BEGIN

SET @DynamicSQL1 += '

Stored Procedures

*****************

' +

(

SELECT DISTINCT

STUFF(

(

SELECT CONCAT(SchemaName, '.', [Name]) + CHAR(13)

FROM @StoredPRocedures

FOR XML PATH('')

, ROOT('Root')

, TYPE

).value('/Root[1]', 'NVARCHAR(MAX)'), 1, 0, ''

)

SET @DynamicSQL1 += '*/

END

DECLARE

@C_ObjectID INT

, @C_SchemaName SYSNAME

, @C_ProcedureName SYSNAME;

DECLARE @Cursor CURSOR;

SET @Cursor = CURSOR FOR

SELECT *

FROM @StoredPRocedures;

OPEN @Cursor;

FETCH NEXT FROM @Cursor

INTO

@C_ObjectID

, @C_SchemaName

, @C_ProcedureName;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DynamicSQL1 += '----------********** ' + QUOTENAME(@C_SchemaName) + '.' + QUOTENAME(@C_ProcedureName) + ' - Start **********----------';

IF(@AddCleanUp = 1)

BEGIN

SET @DynamicSQL1 += '

-- Clean up

IF (OBJECT_ID(''' + @C_SchemaName + '.' + @C_ProcedureName + ''', ''P'') IS NOT NULL)

BEGIN DROP PROC ' + QUOTENAME(@C_SchemaName) + '.' + QUOTENAME(@C_ProcedureName) + '; END

END

SET @DynamicSQL1 += '

-- SP Definition

SELECT @DynamicSQL1 += OBJECT_DEFINITION([object_id])

FROM sys.procedures

WHERE [object_id] = @C_ObjectID;

SET @DynamicSQL1 += '

IF (@AddGrantPermission = 1)

BEGIN

SET @DynamicSQL1 += '

-- Permissions

GRANT EXECUTE ON ' + QUOTENAME(@C_SchemaName) + '.' + QUOTENAME(@C_ProcedureName) + '

TO [' + @PermissionName + '];

SET @DynamicSQL1 += '----------********** ' + QUOTENAME(@C_SchemaName) + '.' + QUOTENAME(@C_ProcedureName) + ' - End **********----------

END

FETCH NEXT FROM @Cursor

INTO

@C_ObjectID

, @C_SchemaName

, @C_ProcedureName;

END

CLOSE @Cursor;

DEALLOCATE @Cursor;

EXEC PL.dbo.usp_LongSQLPrint @DynamicSQL1;

The marked lined 6 to 9 accept the parameters from SQLCMD. Now we need to comment the operators after the equal signs and type a standard T-SQL operators.

DECLARE
	@ListStoredProcedures BIT = 1 -- $(ListStoredProcedures)
	, @AddCleanUp BIT = 1 -- $(AddCleanUp)
	, @AddGrantPermission BIT = 1 -- $(AddGrantPermission)
	, @PermissionName SYSNAME = 'My Permission' -- '$(PermissionName)';

DECLARE

@ListStoredProcedures BIT = 1 -- $(ListStoredProcedures)

, @AddCleanUp BIT = 1 -- $(AddCleanUp)

, @AddGrantPermission BIT = 1 -- $(AddGrantPermission)

, @PermissionName SYSNAME = 'My Permission' -- '$(PermissionName)';

Lines 24 to 27 are the ones that control which SP definition to export.

After we make sure that the T-SQL is exporting everything properly, we need to fix lines 6 to 9 and save the code in file Script Stored Procedures.sql. In this example I save my files in C:\Temp.

Next we need to call the T-SQL with SQLCMD. We create an empty file in C:\Temp and rename it to Script Stored Procedures.bat. Open it in Notepad++ and add the code.

sqlcmd -U MyUsername -P MyPassword -S "ServerName\InstanceName" -d "WideWorldImporters" -i "C:\Temp\Script Stored Procedures.sql" -o "C:\Temp\Exported Stored Procedures.sql" -v ListStoredProcedures=1 -v AddCleanUp=1 -v AddGrantPermission=1 -v PermissionName="My Role"

1	sqlcmd -U MyUsername -P MyPassword -S "ServerName\InstanceName" -d "WideWorldImporters" -i "C:\Temp\Script Stored Procedures.sql" -o "C:\Temp\Exported Stored Procedures.sql" -v ListStoredProcedures=1 -v AddCleanUp=1 -v AddGrantPermission=1 -v PermissionName="My Role"

The options that we use are:

-U – Username
-P – Password
-S – “ServerName\InstanceName”
-d – “DatabaseName”
-i – Input file (“C:\Temp\Script Stored Procedures.sql”)
-o – Output file (“C:\Temp\Exported Stored Procedures.sql”)
-v – Boolean variable to list the exported SPs in the beginning of the result (ListStoredProcedures=1)
-v – Boolean variable to add If SP exists, delete it statement before the Create SP statement (AddCleanUp=1)
-v – Boolean variable to add GRANT EXECUTE ON statement (AddGrantPermission=1)
-v – String variable. The role that the permission is granted to (PermissionName=”My Role”)

To test the package, we browse C:\Temp in Command Prompt (cmd.exe) and execute

C:\Temp>"Script Stored Procedures"

1	C:\Temp>"Script Stored Procedures"

As expected, a new file Exported Stored Procedures.sql is created in C:\Temp.

Keep it simple :-)