Technical Article

Script wich exports to a text file the text of SPs

,

This script exports to a text file the creation text of the stored procedures of your database. If you want, you can change the script to export the text of views or functions.

/*
** Autor Rodrigo Acosta
** Export the code of all sp into a text file
** Fecha 08/04/2003
*/SET NOCOUNT ON
GO
/* If it exists, deletes the tmp table */IF (OBJECT_ID('tempdb.dbo.##SPs')) IS NOT NULL
DROP TABLE ##SPs
GO
/* creates the table wich saves the sp text */CREATE TABLE ##SPs
([text] text)
GO

INSERT INTO ##SPs
([text])
VALUES
('-- Server Name:' + @@SERVERNAME)

INSERT INTO ##SPs
([text])
VALUES
('-- Database Name: ' + DB_NAME())

INSERT INTO ##SPs
([text])
VALUES
('-- Date:' + CONVERT(VARCHAR, GETDATE()))

INSERT INTO ##SPs
([text])
VALUES
('-- Stored prodecure generated automatically by user ' + USER_NAME())

INSERT INTO ##SPs
([text])
VALUES
('GO')
GO
DECLARE @SP AS SYSNAME
DECLARE @str AS VARCHAR(500)

/* cursor wich will save all the sp in the database */DECLARE curSP CURSOR LOCAL FOR
SELECT top 5 name
FROM sysobjects
WHERE xtype = 'P'
ORDER BY name
OPEN curSP
FETCH NEXT FROM curSP INTO @SP
WHILE (@@FETCH_STATUS = 0)
BEGIN
/* insert in the temp table, the text of the sp */SELECT @str = 'EXEC sp_helptext ' + @SP

INSERT INTO ##SPs
EXEC (@str)

INSERT INTO ##SPs
([text])
VALUES
('GO')

PRINT 'Stored Procedure ' + @sp + ' Inserted.'
FETCH NEXT FROM curSP INTO @SP
END
CLOSE curSP
DEALLOCATE curSP


/* Now I generate the file */DECLARE @bcp AS VARCHAR(8000)
DECLARE @Status AS INT

PRINT ''
PRINT 'Generating .txt...'

SELECT @bcp = 'bcp "SELECT * FROM ' + DB_NAME() + '.dbo.##SPs" QUERYOUT "C:\StoredProcs_' + @@SERVERNAME + '.txt" -T -c'

EXEC @Status = master.dbo.xp_cmdshell @bcp, no_output
IF @Status <> 0
BEGIN
PRINT 'An error ocurred while generaring the txt file.'
RETURN
END ELSE
PRINT 'C:\StoredProcs_' + @@SERVERNAME + '.txt file generated succesfully.'

Read 218 times
(1 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating