sqlsql-server-2005stored-proceduresbackupssms

Reputation: 17354

export stored procedures through SQL script

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.

Upvotes: 8

Answers (8)

milan minarovic

Reputation: 123

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

Upvotes: 0

user3246578

Reputation: 1

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

Upvotes: 0

MyItchyChin

Reputation: 14041

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

Upvotes: 4

Sam Greenhalgh

Reputation: 6136

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

Upvotes: 11

user1154664

Reputation: 1392

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.

Upvotes: 0

Mikael Eriksson

Reputation: 138960

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.

Upvotes: 2

Santhosh_ms3

Reputation: 110

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.

Upvotes: 0

Ivo

Reputation: 3436

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)

Upvotes: 5

export stored procedures through SQL script

Answers (8)

Related Questions