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

This script will send the contents of all the stored procedures, functions and triggers in a database to a text file, eliminating the need to go through every script to find what you're looking for.

This Content Component encountered an error
This Content Component encountered an error

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 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

Dig deeper on Stress, Load and Software Performance Testing

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchSOA

TheServerSide

SearchCloudApplications

SearchAWS

SearchBusinessAnalytics

SearchFinancialApplications

SearchHealthIT

Close