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:

1. SQL Agent Job
2. SQLCMD
3. T-SQL (Input) – Script Stored Procedures.sql
4. 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

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

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

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"

• -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"

Keep it simple :-)