Blogs - Code to dump SQL Objects (stored procedures, views) to a text file

DEVSHED Blog

As always, I welcome your comments!

Code to dump SQL Objects (stored procedures, views) to a text file

Apr 16

Written by: Steve Gray
4/16/2010 11:17 AM

This code will dump the text of any object that you want to a text file. I use it as a backup, and to easily search a code base for something.

First, enable ‘Automation Procedures’ using this script:

Code Snippet
 sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
 

This grant permissions:

Code Snippet
 grant exec on sp_oacreate to myUser
grant exec on sp_oaMethod to myUser
grant exec on sp_oaDestroy to myUser
 

Then run this script to dump your objects. You’ll need to configure the line that set the name and location of the output file, and the query that decides what objects to grab.

Code Snippet
 --runs all the stored procs and triggers in the current db to a text file
DECLARE 
    @vchrFile VARCHAR(1000) 
--initialize
 
DECLARE @db varchar(15)
DECLARE @dt varchar(20)
set @db = upper(db_name())
set @dt = convert(varchar(10),getdate(),112)
 
 
SET @vchrFile = 'h:\NGB-SQL-02.' + @db + ' ' + @dt + '.txt'
 
 
 
 
DECLARE 
    @vchrFileID INT ,
    @FS INT ,
    @RC INT ,
    @vchrStoredProcName varchar(8000),
    @vchrChar varchar(1),-- holds the current character that we are evaluating
    @vchrLine varchar(8000),-- holds the line that we are about to print
    @intPos int,
    @vchrPrevChar varchar(1),
    @intAscii int,
    @intPrevAscii int,
    @vchrSysCommentText varchar(8000)
 
 
 
 
 
--=======================================================================
-- open the output file
--=======================================================================
EXEC @RC = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
 
IF @RC <> 0
    PRINT 'Error:  Creating the file system object'
 
-- Opens the file specified by the @vchrFile input parameter
EXEC @RC = sp_OAMethod @FS , 'OpenTextFile' , @vchrFileID OUT , 
    @vchrFile , 8 , 1
 
-- Prints error if non 0 return code during sp_OAMethod OpenTextFile execution 
IF @RC <> 0 begin
    PRINT 'Error:  Opening the specified text file'
    return
end
 
--======================================================================
--gather data on stored procedure into table _dbText
--======================================================================
DECLARE curStoredProcs CURSOR KEYSET FOR 
    SELECT Name from sysobjects 
        where 
            (name like '_4%'
            or name like 'ep%'
            or name like 'f%'
            or name like 'fp%'
            or name like 'sp%'
            or name like 't_4%'
            or name like 'vw%')
        order by 1
--select top 100 * from sysobjects
 
OPEN curStoredProcs
 
FETCH NEXT FROM curStoredProcs INTO @vchrStoredProcName
 
WHILE (@@fetch_status = 0) BEGIN
    set @vchrLine = '#####################################################' 
    EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
    IF @RC <> 0 PRINT 'Error:  Writing string data to file'
 
    set @vchrLine = '' 
    EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
    IF @RC <> 0 PRINT 'Error:  Writing string data to file'
 
    set @vchrLine = @vchrStoredProcName 
    EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
    IF @RC <> 0 PRINT 'Error:  Writing string data to file'
 
    set @vchrLine = '' 
    EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
    IF @RC <> 0 PRINT 'Error:  Writing string data to file'
 
    set @vchrLine = '#########################################################' 
    EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
    IF @RC <> 0 PRINT 'Error:  Writing string data to file'
    
    --initialize
    select 
        @vchrLine = '',
        @vchrPrevChar = ''
 
    -- =============================================
    -- loop through one stored proc
    -- =============================================
    DECLARE curComments  CURSOR LOCAL FOR 
        SELECT [text] FROM syscomments 
            WHERE id = OBJECT_ID(@vchrStoredProcName) and encrypted = 0
            ORDER BY number, colid
        FOR READ ONLY
    
    OPEN curComments
    
    FETCH NEXT FROM curComments into @vchrSysCommentText
    
    --loop through the lines in the syscomments table. 
    --there can be one or many for the stored proc, 
    --many stored proc lines can be on one syscomments line
    WHILE @@fetch_status >= 0
    BEGIN
        --initialize
        select 
            @intPos = 1
    
        WHILE @intPos  <> len(@vchrSysCommentText) BEGIN
            select @vchrChar = substring(@vchrSysCommentText,@intPos,1)
            select @intAscii = ascii(@vchrChar)
 
            if not (@intAscii = 13 or @intAscii = 10)
                select @vchrLine = @vchrLine + @vchrChar
    
            --if we encounter a line feed...
            if @intAscii in (10,13) and @intPrevAscii in (10,13) begin
                --output a line and clear the line buffer
 
               -- Appends the string value line to the file specified by the 
                --@vchrFile input parameter
                EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
            
               -- Prints error if non 0 return code during 
                --sp_OAMethod WriteLine execution
                IF @RC <> 0 
                        PRINT 'Error:  Writing string data to file'
    
                select @vchrLine = ''
            end
    
    
            select @intPos = @intPos + 1,
                @vchrPrevChar = @vchrChar,
                @intPrevAscii = @intAscii
    
        END
    
        FETCH NEXT FROM curComments into @vchrSysCommentText
    END
 
    CLOSE curComments
    DEALLOCATE curComments
 
 
    set @vchrLine = '' 
    EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
    IF @RC <> 0 PRINT 'Error:  Writing string data to file'
    set @vchrLine = '' 
    EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
    IF @RC <> 0 PRINT 'Error:  Writing string data to file'
    
 
    FETCH NEXT FROM curStoredProcs INTO @vchrStoredProcName
END
 
CLOSE curStoredProcs
DEALLOCATE curStoredProcs
 
EXECUTE @RC = sp_OADestroy @vchrFileID
EXECUTE @RC = sp_OADestroy @FS
 

Trackback Print

Tags:

Categories:

Location: Blogs Parent Separator

DEVSHED Blogs Child Separator

SQL

0 comment(s) so far...

As always, I welcome your comments!