Tip

Create one text file that contains all the stored procedures and triggers in a database

This tip was submitted by Steve Gray who operates a blog on VSToolsForum.com.

This script will send the contents of all the stored procedures, functions, and triggers in a database to a text file. In fact, it will send

    Requires Free Membership to View

any object that can be found in SELECT * FROM SYSOBJECTS

Why is that useful? For me, it's indispensable. The Dynamics GP database (Microsoft's accounting package) that I work in has north of 34,000 objects. If I change something, or need to know where a reference is, or the boss wants to know what stored procedure is deleting the sales orders every night, I have to play hide-and-go-seek through all the scripts.

What if someone proposes changing the name of a database field? Having the text of all my object in one file enables me to find all occurrences of a field name in any of my scripts. Woo-Ha! Now I know the name of the stored proc or trigger that I have to change.

In the past I would just right click on the server name in Enterprise Manager, and choose 'Generate Scripts'. That gave me exactly what I needed, but in a database this size it took almost an hour. This script runs in about a minute.

Additionally, it gives me a history. If I run this periodically, I am able to search past versions quickly and see what I changed.

I'm not going to go into detail about how it works, but I'd be happy to if anyone shows an interest. Just drop me a line.



IF EXISTS (SELECT name
    FROM   sysobjects
    WHERE  name = N'_4P_dbText1'
    AND    type = 'P')
    DROP PROCEDURE _4P_dbText1
GO

CREATE PROCEDURE _4P_dbText1
 


AS
 

 

DECLARE
 @vchrFile VARCHAR(1000) ,
 @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)

 

--initialize

SET @vchrFile = '\\ngb-sql-03\c$\export.txt'


-- =================================================================
-- 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
 PRINT 'Error:  Opening the specified text file'

-- =================================================================
-- gather data on stored procedure into table _dbText
-- =================================================================
DECLARE curStoredProcs CURSOR KEYSET FOR
 SELECT Name from sysobjects
  where [name] like '_4P%'
   or [name] like 'f%'
   or [name] like 'sp%'
   or [name] like 't_4p%'
  order by Name

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

go

grant all on _4P_dbText1 to public


This was first published in April 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.