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

SearchSOA

TheServerSide

SearchCloudApplications

SearchAWS

SearchBusinessAnalytics

SearchFinancialApplications

SearchHealthIT

Close