generate script for all stored procedures for the given database. [ All Languages » SQL Server »  Stored Procedure] Total Hit ( 6764) Rate this article:     Poor     Excellent   Submit Your Question/Comment about this article Rating

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

Related Article(s)

Send fax from SQL Server using Microsoft Word

Export data to excel file

Export data to word file

Working with COM objects from within T-SQL

OLE Automation in SQL server

Exploring SQL Server Triggers

Using FTP in Transact-SQL

Regular Expressions in T-SQL

Stored procedure which displays current or archived SQL Server Log

Stored procedure to Send SMTP mail from SQL SERVER 2000 (Email Body max 8000 chars)

Create Excel XLS from T-SQL

How do I script all the objects from all databases on a server and record changes

Generate Scripts for SQL Server Objects

Working with COM Objects from within T-SQL

How to display COM error description in SQL Stored procedure ?

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 (887) submissions by this author  (Birth Date : 7/14/1981 )