-- COURTESY OF :
--
Steve Graysp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'GetAllProcs'
AND type = 'P')
DROP PROCEDURE GetAllProcs
GO
CREATE PROCEDURE GetAllProcs
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 = 'C:\temp\export.sql'
--===================================================================================
-- 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 s.name from sysobjects s where type = 'U' order by Name
/*
-- COPY PASTE THE DESIRED TYPES OF OBJECTS ABOVE
--HOW-TO LIST ALL PROCEDURE IN A DATABASE
-- select s.name from sysobjects s where type = 'P' order by Name
--HOW-TO LIST ALL TRIGGERS BY NAME IN A DATABASE
-- select s.name from sysobjects s where type = 'TR' order by Name
--HOW-TO LIST TABLES IN A DATABASE
select s.name from sysobjects s where type = 'U' order by Name
--how-to list all system tables in a database
-- select s.name from sysobjects s where type = 's' order by Name
--how-to list all the views in a database
-- select s.name from sysobjects s where type = 'v' 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
Posts
