export stored procedures through SQL script

Question

Was: How to back up selected stored procedure using query

I would like to backup 10 out of 200 stores procedures over command line ( In SQL Server Management Studio). Is there an easy way to do it?

Right now I am using the Database->Tasks->Generate Scripts option, that takes me through a series of dialog where I choose the SP that I want to export. I would like to make this process easy, so I don't have to do it all over again.

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

BTW saving to a textfile is not important, just printing the result in SSMS is good enough, I can copy and save that myself. — TheTechGuy
– TheTechGuy, Commented Jul 25, 2011 at 14:16
Can you make a small console application that reads the SP definitions and writes them to text files? Not sure how you would do it all in tsql...it would get ugly with cursors and print statements. — dotjoe
– dotjoe, Commented Jan 28, 2012 at 17:13
I mean this is acceptable to me as we'll. All procedure that I updated after Jan-2012, I would like to export those only. That will actually solve my problem. — TheTechGuy
– TheTechGuy, Commented Jan 28, 2012 at 19:39
When you say "command line", do you mean "cmd.exe" command line, or SQL Servery query window? — Nick Vaccaro
– Nick Vaccaro, Commented Feb 2, 2012 at 16:42
@Norla, not not cmd.exe. By that I really mean SQL Server Management Query. Thanks. Please look at the some queries down. They have helped me but not entirely. — TheTechGuy
– TheTechGuy, Commented Feb 2, 2012 at 16:45

Sam Greenhalgh · Accepted Answer · 2012-02-02 15:35:31Z

11

+50

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 sounds like you might want something like this to include the LAST_ALTERED date and Definition in a result set.

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

edited Feb 2, 2012 at 15:35

answered Feb 1, 2012 at 20:18

Sam Greenhalgh

6,14624 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TheTechGuy Over a year ago

Super. One problem, it truncates the query if it is longer than 4000 characters. Is there a way to address that. It would also be fine with me if you pull individual store procedure as separate records. If you want to edit, please add new query, leave this as it is.

TheTechGuy Over a year ago

Also there is a way to give it lastmodify date or pass stored procedure names

Travis Over a year ago

This was great! I had to add a filter so it only returned my user created procedures

Ivo · Accepted Answer · 2011-11-22 15:44:28Z

5

You can select the SP's that 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

edited Nov 22, 2011 at 15:44

answered Jul 25, 2011 at 14:08

Ivo

3,4465 gold badges35 silver badges56 bronze badges

6 Comments

TheTechGuy Over a year ago

I see them only listed as 10 records, I would like it to print the structure in SSMS. working on it

Ivo Over a year ago

What about adding the result of the 'modu.definition' to a temp table . Then looping throught that temp table and print each result. You only need 20 records so you could use a cursus

TheTechGuy Over a year ago

I would like to select this answer but it prints all SP in one line, is there a way to improve the readability?

TheTechGuy Over a year ago

Ivo, Can you change so new line characters are included in the result? That seems to be the only problem.

Ivo Over a year ago

You can get a new line by adding char(13) and char(10) so something like this, modu.definition + CHAR(13) + CHAR(10) AS SPDefinition

|

MyItchyChin · Accepted Answer · 2012-02-03 03:34:34Z

4

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 named BackUpSprocs.SQL then you can run a command similar to the following to get your output to a file.

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

edited Feb 3, 2012 at 3:34

answered Feb 1, 2012 at 20:09

MyItchyChin

14.1k1 gold badge27 silver badges45 bronze badges

3 Comments

TheTechGuy Over a year ago

This works great but it does not pull the longer one. May be it was designed to work that way?

MyItchyChin Over a year ago

@Thecrocodilehunter : My bad, the PRINT statement only prints 4000 Characters at a time. Be aware this will definitely fail if your sproc happens to have 4000 contiguous characters without a CRLF occurring, however unlikely that may be.

TheTechGuy Over a year ago

I see so it is the limitation of print, I will store the output to a file and see how it works.

Mikael Eriksson · Accepted Answer · 2012-01-28 20:45:56Z

2

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 on the link in the result pane to see the entire script.

edited Jan 28, 2012 at 20:45

answered Jan 28, 2012 at 15:15

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

10 Comments

TheTechGuy Over a year ago

Can I do select routine_name1,routine_name2, etc because that is what I want.

Mikael Eriksson Over a year ago

@Thecrocodilehunter - Don't think I understand what you want. Do you want to specify the procedures you want instead of fetching all procedures or...?

TheTechGuy Over a year ago

I would like to have both options. But if any one works, that is still acceptable.

Mikael Eriksson Over a year ago

@Thecrocodilehunter Ok. I have updated answer with a where clause that filters the procedures exported by name(s). If you want all procedures just don't use it.

TheTechGuy Over a year ago

I tried the Update1 query, it does not print the entire subroutine, just two lines. Basically the same result that I got from answer1 a while ago.

|

shanethehat · Accepted Answer · 2012-01-28 17:08:35Z

0

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 out. This may help you.

edited Jan 28, 2012 at 17:08

shanethehat

15.6k11 gold badges59 silver badges87 bronze badges

answered Jan 28, 2012 at 14:49

Santhosh_ms3

1102 gold badges3 silver badges15 bronze badges

1 Comment

TheTechGuy Over a year ago

obj.create_date should be SO.created_date, but it still does not give me the output that I want.

user1154664 · Accepted Answer · 2012-02-02 15:18:06Z

0

Not an SQL way, but I think you could automate your Database->Tasks->Generate Scripts option/...More Steps by using autoit or Sikuli or maybe some other GUI testing tool.

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

AutoIt v3 is a freeware BASIC-like scripting language designed for automating the Windows GUI and general scripting.

answered Feb 2, 2012 at 15:18

user1154664

1,39013 silver badges11 bronze badges

Comments

user3246578 · Accepted Answer · 2014-01-28 22:31:07Z

I edited MyItchyChin's script since I've found some flaws in it. It would loop indefinetely when @part < 0 and would not correctly print the "END" keyword that closes the procedure code ("create procedure ... as begin...END"). I did other minor changes too. My problems are now solved! Many thanks to MyItchyChin for the initial script.

Obs: I use this script in SQL Server 2008 R2. The script can also be used to 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

milan minarovic · Accepted Answer · 2015-05-22 12:45:04Z

I have create following Procedure which goes thru all SP and Views in particular DB (can be extend to Functions, ...) and store each code script one by one into TXT files. Tested ON MS SQL 2008 R2 and 2014

First part Inserting all scripts of SP and Views into Temp table. Then later using BCP utility. If you wish you can can use for export SSIS package and not SP as I did 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 thru temp table creating file name with Prefix P_ or V_ and with suffix as Date in 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

Collectives™ on Stack Overflow

export stored procedures through SQL script

8 Answers 8

3 Comments

6 Comments

3 Comments

10 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

6 Comments

3 Comments

10 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related