Atlanta Custom Software Development 

   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools


Click here to copy the following block
/*
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


Audit, Notify, Deploy and Manage SSIS
Download the Free 30-day Trial Version...Learn More About This Product...

Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )

Recommanded Links

 


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  Blogs

© 2008 BinaryWorld LLC. All rights reserved.