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
