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
When you register, you'll receive targeted emails designed to keep you informed of the most relevant information on Agile development, application security, testing & QA, software requirements, and more.
Hannah Smalltree, Editorial Director
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