Create script for each Store Procedure and save each script into Seperate SQL File

Posted Date: 04 Nov 2009	Resource Type: Code Snippets	Category: SQL
Author: Lakhan Pal	Member Level: Diamond
Rating:	Points: 25 (Rs 5)

With the help of sqlcmd utility we can save the script of store procedure in separate file for each store procedure.

Name of the file will be same as the name of the store procedure.
Sample Code to Create Script and Save:


    select N'sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext ' + name + N' " -h-1 -k 1 >> c:\DB\sp\' + name + N'.sql' from sys.objects where type='P'

When you will execute the above command the following output will be generated based on the number of store procedure in your database:


    sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext User_Authenticate_sp " -h-1 -k 1 >> c:\DB\sp\User_Authenticate_sp.sql
    sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext User_GetPassword_sp " -h-1 -k 1 >> c:\DB\sp\User_GetPassword_sp.sql
    sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_upgraddiagrams " -h-1 -k 1 >> c:\DB\sp\sp_upgraddiagrams.sql
    sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_helpdiagrams " -h-1 -k 1 >> c:\DB\sp\sp_helpdiagrams.sql
    sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_helpdiagramdefinition " -h-1 -k 1 >> c:\DB\sp\sp_helpdiagramdefinition.sql

Save these output data into a batch file execute this batch file. this will save the store procedure script on C:\DB\sp

Thanks & Regards
Lakhan Pal Garg

Responses

No responses found. Be the first to respond and make money from revenue sharing program.

Feedbacks

Popular Tags

What are tags ?

Search Tags

SqlCMD . SQL Server . Database . Create Store Procedure Script .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Store Procedure to Generate Create Table Script
	Previous Resource: How to determine First Day of the week From Week Number and Year in SQL Server?
	Return to Discussion Resource Index
	Post New Resource
	Category: SQL

Post resources and earn money!

More Resources