Binaryworld - generate script for all stored procedures for the given database. ... [ SQL Server -> Stored Procedure ]

/*
This script will generate script for all stored procedures for the
given database.
You can pass the Server name, user name, user password, database name
and file name into GenerateSP stored procedure, as in the example
below:

EXEC GenerateSP @server = 'Server_Name',
            @uname = 'User_Name',
            @pwd = 'Password',
            @dbname = 'Database_Name',
            @filename = 'c:\script.sql'

You can specify different number of parameters (from zero to five parameters).
If you do not specify server name, then the current server will be used;
if you do not specify user name, then the current user name will be used;
if you do not specify database name, then the current database will be used;
if you do not specify file name, then script will be placed into file
script.sql on drive 'c:'; if you do not specify password, the password
will not be used (for example: username = 'sa' and empty password, you can
pass in this case only username).

My stored procedure can be used for learning some general SQL Server
features (how to work with OLE objects from the SQL Server, how to use
some system functions, how to work with cursors and so on).
*/

IF OBJECT_ID('GenerateSP') IS NOT NULL DROP PROC GenerateSP
GO

CREATE PROC GenerateSP (
@server varchar(30) = null,
@uname varchar(30) = null,
@pwd varchar(30) = null,
@dbname varchar(30) = null,
@filename varchar(200) = 'c:\script.sql'
)
AS

DECLARE @object int
DECLARE @hr int
DECLARE @return varchar(200)
DECLARE @exec_str varchar(2000)
DECLARE @spname sysname

SET NOCOUNT ON

-- Sets the server to the local server
IF @server is NULL
SELECT @server = @@servername

-- Sets the database to the current database
IF @dbname is NULL
SELECT @dbname = db_name()

-- Sets the username to the current user name
IF @uname is NULL
SELECT @uname = SYSTEM_USER

-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
PRINT 'error create SQLOLE.SQLServer'
RETURN
END

-- Connect to the SQL Server
IF @pwd is NULL
BEGIN
  EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @server, @uname
  IF @hr <> 0
   BEGIN
    PRINT 'error Connect'
    RETURN
   END
END
ELSE
BEGIN
  EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @server, @uname, @pwd
  IF @hr <> 0
   BEGIN
    PRINT 'error Connect'
    RETURN
   END
END

--Verify the connection
EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
IF @hr <> 0
BEGIN
PRINT 'error VerifyConnection'
RETURN
END

SET @exec_str = 'DECLARE script_cursor CURSOR FOR SELECT name FROM ' + @dbname + '..sysobjects WHERE type = ''P'' ORDER BY Name'
EXEC (@exec_str)

OPEN script_cursor
FETCH NEXT FROM script_cursor INTO @spname
WHILE (@@fetch_status <> -1)
BEGIN
SET @exec_str = 'Databases("'+ @dbname +'").StoredProcedures("'+RTRIM(UPPER(@spname))+'").Script(74077,"'+ @filename +'")'
EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
IF @hr <> 0
  BEGIN
   PRINT 'error Script'
   RETURN
  END
FETCH NEXT FROM script_cursor INTO @spname
END
CLOSE script_cursor
DEALLOCATE script_cursor

-- Destroy the object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'error destroy object'
RETURN
END
GO