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.'

Rate

Share

Share

Rate