Send the Contents of all the Stored Procedures, Triggers, and Functions to a Text File

steve gray — Sat, 24 Mar 2007 20:39:00 GMT

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 indispensible. The Dynamics GP database (Microsoft's accounting package) that I work in has north of 34000 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 occurances 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 you get ole automation errors, run this script first.

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

As always, comments are welcome.

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

CREATE PROCEDURE _4P_dbText1

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

grant all on _4P_dbText1 to public

SQL Server (T-SQL) : text file

Send the Contents of all the Stored Procedures, Triggers, and Functions to a Text File