SQL 2005 scripting stored procedures

SQL 2005 scripting stored procedures

• RFaircloth

  It is frustrating. MS has taken a step backward in the scripting of stored procedures.  There is no obvious way to script the permission along with the code either.

  
  
  

• Anatoly Stefanyuk

  Hi,
  
  I do not test in SQL Server 2005 but SCPTXFR.EXE is a very good command line utility for it. It can be found in the C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade (SQL Server 2000) or x86\upgrade in SQL Server 2000 CD. It appears to be missing in SQL Server 2005 but....
  
  More info.. http://www.norbtechnologies.com/pdf/Technical%20Article%20-%20How%20to%20Schedule%20a%20SQL%20Server%20Database%20Creation%20Script.pdf
  
  regards
  Nilton Pinheiro
  www.mcdbabrasil.com.br (Portuguese)
  

   

  
  
  

• Mark Leung

  Hi.
  
  You should be able to glue something together with
  select * from sys.procedures,
  create cursor
  and
  sp_helptext
  
  Happy proceduring
  
  

• Ethan Hunt

  I don't believe there is an easy way to script multiple objects to multiple files in Management Studio. 
  
  When I highlight multiple functions in the summary list control and select "Script Function as > Create to > File..." the file that is created has a comment preceding each object's definition like "/****** Object: UserDefinedFunction [dbo].[ufnGetAccountingEndDate] Script Date: 11/08/2005 15:00:19 ******/" As a workaround, it should be straight-forward to tease this file apart using Awk, Perl or some other scripting language.
  
  
  
  

• Larry Buerk

  Well, knowing that we can do this: SELECT Object_Definition(Object_ID) FROM sys.procedures The following might seem like a real brute force approach, but this will generate the command line scripts necessary to do this (don't forget to use the correct server and database, and change -E to -U/-P if using SQL auth): select 'osql -S -E server -d database -Q"SELECT Object_Definition(Object_ID) from sys.procedures WHERE name='''+name+'''" > c:\'+name+'.PRC' FROM sys.procedures Take the output, paste into a .bat file, and run. (You may wish to modify the result first, e.g. leave out specific procedures.) This should make the generation of the files pretty painless, though they will not include any of the typical SET options ON at the top and OFF at the end. Of course, you could take this further, like writing these commands directly to a batch file by executing xp_cmdshell directly, and then even executing the file when you're done. But I was too lazy to deal with the nesting quotes, which would quickly get far beyond anything I'd have the patience for right now. :-) I also didn't want to get into the process of enabling xp_cmdshell, which is disabled by default, and for good reason. If you have enabled xp_cmdshell, feel free to experiment. wrote in message news:dc1b3522-9bef-4ef9-b4c8-c5cc39711ee2@discussions.microsoft.com... > I'm wondering if there is an automated way to script a large number of > stored procedures to individual files, for later inclusion in a source > safe project > Preferrably they would have the same name as the sp in the database. > > In the sql 2005 studio, I can script them one by one, or a bunch of them > to one file, but I can not see how to get them to individual files. > > Thanks for any suggestions. > > LT >
  
• SQL 2005 scripting stored procedures