Export stored procedures via SQL script

Question

Was: How to back up a selected stored procedure using a query

I would like to backup 10 out of 200 storage procedures through the command line (in SQL Server Management Studio). Is there an easy way to do this?

Right now I am using the Database-> Tasks-> Generate Scripts option, which guides me through a series of dialogs in which I select the SP that I want to export. I would like to make this process easy, so I don’t have to do it again and again.

Note. Export. I mean just print it on the screen so that I can copy it and save it in a text file.

Answer 1

How about using INFORMATION_SCHEMA.Routines?

DECLARE MY_CURSOR Cursor FOR SELECT r.Routine_Definition FROM INFORMATION_SCHEMA.Routines r OPEN MY_CURSOR DECLARE @sproc VARCHAR(MAX) FETCH NEXT FROM MY_CURSOR INTO @sproc WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) PRINT @sproc FETCH NEXT FROM MY_CURSOR INTO @sproc END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR GO 

---

EDIT

It looks like you might want something like this to include the LAST_ALTERED date and the definition in the result set.

 SELECT r.LAST_ALTERED, r.ROUTINE_NAME, r.Routine_Definition FROM INFORMATION_SCHEMA.Routines r 

Answer 2

You can select the SP you want with the following query:

 SELECT obj.Name as SPName, modu.definition as SPDefinition, obj.create_date as SPCreationDate FROM sys.sql_modules modu INNER JOIN sys.objects obj ON modu.object_id = obj.object_id WHERE obj.type = 'P' AND obj.Name IN ('sp1','sp2', ect) 

See also: http://www.sqlservercurry.com/2009/03/list-all-stored-procedures-of-database.html and http://www.sqlservercurry.com/2007/12/redirect-select-query -output-to-text.html

Answer 3

The following SQL should do what you want.

 SET NOCOUNT ON DECLARE @procs AS TABLE( object_id INT , definition NVARCHAR(MAX) , uses_ansi_nulls BIT , uses_quoted_identifier BIT ) INSERT INTO @procs SELECT m.object_id , m.definition , m.uses_ansi_nulls , m.uses_quoted_identifier FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.object_id = o.object_id WHERE o.type = 'P' --Change this part to suit your needs... AND o.name IN ('sproc1' ,'sproc2' ,'sproc3' ) --Optionally filter by date? --AND o.create_date >= '02/01/2012' DECLARE @endStmt NCHAR(6) , @object_id INT , @definition NVARCHAR(MAX) , @uses_ansi_nulls BIT , @uses_quoted_identifier BIT SELECT @object_id = MIN(object_id) , @endStmt = CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM @procs WHILE ISNULL(@object_id,0) > 0 BEGIN SELECT @definition = definition , @uses_ansi_nulls = uses_ansi_nulls , @uses_quoted_identifier = uses_quoted_identifier FROM @procs IF @uses_ansi_nulls = 1 PRINT 'SET ANSI_NULLS ON' + @endStmt ELSE PRINT 'SET ANSI_NULLS OFF' + @endStmt IF @uses_quoted_identifier = 1 PRINT 'SET QUOTED_IDENTIFIER ON' + @endStmt ELSE PRINT 'SET QUOTED_IDENTIFIER OFF' + @endStmt IF LEN(@definition) <= 4000 PRINT @definition ELSE BEGIN DECLARE @crlf VARCHAR(2), @len BIGINT, @offset BIGINT, @part BIGINT SELECT @crlf = CHAR(13)+CHAR(10) , @len = LEN(@definition) , @offset = 1 , @part = CHARINDEX(@crlf,@definition)-1 WHILE @offset <= @len BEGIN PRINT SUBSTRING(@definition,@offset,@part) SET @offset = @offset + @part + LEN(@crlf) SET @part = CHARINDEX(@crlf,@definition,@offset) -@offset END END PRINT @endStmt SELECT @object_id = MIN(object_id) FROM @procs WHERE object_id > @object_id END 

If you save the above SQL as a file with the name BackUpSprocs.SQL, you can run a command similar to the one below to get your output to the file.

 SQLCMD -E -S SQLSERVER_NAME -d DATABASE_NAME -i BackUpSprocs.SQL -o Sprocs.txt 

Answer 4

 declare @S nvarchar(max) set @S = N'' select @S = @S + [definition] + nchar(13) + nchar(10) + N'GO' + nchar(13) + nchar(10) from sys.sql_modules as m inner join sys.objects as o on m.object_id = o.object_id where o.create_date > '20120101' and o.name in ('Proc1', 'Proc2', 'ProcN') select @S for xml path('') 

Click the link in the results pane to see the whole script.

Answer 5

 SELECT SO.Name as Name, SM.definition as SPDefinition, obj.create_date as CreationDate FROM sys.sql_modules SM INNER JOIN sys.objects SO ON SM.object_id = SO.object_id WHERE SO.type = 'P' AND SO.create_date >= '01-01-2012' 

Check this. This can help you.

Answer 6

Not the SQL way, but I think that you could automate your database -> Tasks -> Create scripts / Extra steps with autoit or Sikuli, or maybe some other GUI testing tool.

I tried a little Sikuli for the web, and it was not 100% reliable for me.

AutoIt v3 is a free BASIC-based scripting language designed to automate the Windows GUI and common script.

Answer 7

I edited the MyItchyChin script as I discovered some flaws. This will be cyclically indefinite when @part <0 and will not correctly print the keyword "END", which closes the procedure code ("create procedure ... as begin ... END "). I made other minor changes. Now my problems are solved! Many thanks to MyItchyChin for the source script.

Obs: I use this script in SQL Server 2008 R2. script can also be used for script functions.

  SET NOCOUNT ON DECLARE @procs AS TABLE( nome varchar(200),object_id INT , definition NVARCHAR(MAX) , uses_ansi_nulls BIT , uses_quoted_identifier BIT ) INSERT INTO @procs SELECT o.name ,m.object_id , m.definition , m.uses_ansi_nulls , m.uses_quoted_identifier FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.object_id = o.object_id WHERE 1=1 --and o.type = 'P' AND o.name IN ('proc1') DECLARE @endStmt NCHAR(6) , @object_id INT , @definition NVARCHAR(MAX) , @uses_ansi_nulls BIT , @uses_quoted_identifier BIT DECLARE @crlf VARCHAR(2), @len BIGINT, @offset BIGINT, @part BIGINT SELECT @object_id = MIN(object_id) , @endStmt = CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM @procs declare c cursor for SELECT definition , uses_ansi_nulls , uses_quoted_identifier FROM @procs order by nome asc open c fetch next from c into @definition,@uses_ansi_nulls,@uses_quoted_identifier while @@fetch_status<>-1 begin IF @uses_ansi_nulls = 1 PRINT 'SET ANSI_NULLS ON' + @endStmt; ELSE PRINT 'SET ANSI_NULLS OFF' + @endStmt; IF @uses_quoted_identifier = 1 PRINT 'SET QUOTED_IDENTIFIER ON' + @endStmt; ELSE PRINT 'SET QUOTED_IDENTIFIER OFF' + @endStmt; --PRINT @definition; IF LEN(@definition) <= 4000 PRINT @definition ELSE BEGIN SELECT @crlf = CHAR(13)+CHAR(10) , @len = LEN(@definition) , @offset = 1 , @part = CHARINDEX(@crlf,@definition)-1 WHILE @offset <= @len AND @part>=0 BEGIN --PRINT @offset --PRINT @part --PRINT LEN(@crlf) --PRINT @len PRINT SUBSTRING(@definition,@offset,@part) SET @offset = @offset + @part + LEN(@crlf) SET @part = CHARINDEX(@crlf,@definition,@offset) -@offset --PRINT @offset --PRINT @part --PRINT @len IF @part < 0 PRINT SUBSTRING(@definition,@offset,100) END END PRINT @endStmt; fetch next from c into @definition,@uses_ansi_nulls,@uses_quoted_identifier end close c deallocate c 

Answer 8

I have the following procedure that goes through all the SPs and views in particular DB (can be expanded to functions, ...) and store each script code one by one in TXT files. Tested on MS SQL 2008 R2 and 2014

Part One Insert all SP and Views scripts into a Temp table. Then use the BCP utility. If you want, you can use the SSIS package to export, not SP, as in this example.

 DECLARE @File_name AS VARCHAR(255) ,@Folder_path AS VARCHAR(255) ,@File_Path_Name AS VARCHAR(255) ,@CMD AS VARCHAR(8000) IF OBJECT_ID('tempdb..#TEMP_AA') IS NOT NULL DROP TABLE #TEMP_AA; SELECT T1.NAME AS ObjectName ,T1.type AS ObjectType ,STUFF(((SELECT ' ' + T.[TEXT] FROM (SELECT SC.[id],SC.colid,SC.[TEXT] FROM SYSCOMMENTS sc ) AS T WHERE T.[id] = T1.[id] ORDER BY T.colid FOR XML PATH(''),TYPE ).value('.[1]', 'NVARCHAR(MAX)') ), 1, 1, '') AS ObjectText INTO #TEMP_AA FROM SYSOBJECTS AS T1 WHERE 1=1 AND T1.type IN ('P', 'V') /* Procedures and Views*/ AND NOT T1.[name] LIKE 'dt_%' 

Loop goes through the temp table, creating a file name with the prefix P_ or V_ and with the suffix as Date in the format YYYYMMDD:

 -- Exporting Scripts one by one into TXT files WHILE (SELECT TOP 1 objectName FROM #TEMP_AA) IS NOT NULL BEGIN SELECT TOP 1 @File_name = RTRIM(LTRIM(ObjectType)) + '_' + ObjectName +'_' + REPLACE(CAST(CAST(GETDATE()AS DATE) AS VARCHAR),'-','') FROM #TEMP_AA; IF OBJECT_ID('tempdb..##TEMP_BB') IS NOT NULL DROP TABLE ##TEMP_BB; CREATE TABLE ##TEMP_BB (ObjectText VARCHAR(MAX)); INSERT INTO ##TEMP_BB SELECT TOP 1 ObjectText FROM #TEMP_AA; --'Setting File name' SET @Folder_Path = 'C:\AAAA\' SET @File_Path_Name = @Folder_Path + @File_name + '.txt' SET @CMD ='BCP ##TEMP_BB OUT "' +@File _Path_Name+'" -T -c -t "Your Server"' -- 'Output via BCP into TXT file' EXEC xp_cmdshell @CMD; --Delete Line From temp which has been procese already WITH CTE AS (SELECT TOP 1 * FROM #TEMP_AA) DELETE FROM CTE; END