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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
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 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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; 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 |
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.
1 2 3 4 5 |
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.
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
1 |
C:\Temp>"Script Stored Procedures" |
As expected, a new file Exported Stored Procedures.sql is created in C:\Temp.
Keep it simple :-)