.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Create Script File For Each Store Procedure And Save into Seperate SQL File For Each

Posted By:Lakhan Pal Garg       Posted Date: October 28, 2009    Points: 10    Category: SQL Query    URL: http://www.dotnetspark.com  
 

Create Script File For Each Store Procedure And Save into Seperate SQL File For Each.

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
Free Code Snippets

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend