CodePrairie .NET

South Dakota .NET User Group


Generating stored procedures to multiple files - SQL2005

Remember that functionality in Enterprise Manager that let you generate scripts for all your stored procedures to multiple files? I guess no one used that feature because in SQL Management Studio (2005) that feature was removed.

I found some information here that says it was put back in SP2. However SP2 is a 292MB download. So I started the download, but while it was downloading I figured I'd just write my own script to do this for me.

I saw some forum posts saying you could do this all using SQL, but that just sounded boring so I thought I'd use Ruby instead.

  1 require 'ftools' 
  2 File.makedirs 'split_sp' 
  4'storedprocs.sql','r') do |file| 
  6   text = 
  8   text.scan(/CREATE PROCEDURE \[dbo\]\.\[(API_\w+)\]\s*(.*?)'\s*^END$\s*^GO$/m) do |match| 
  9"split_sp/#{match[0]}.sql",'w') do |write_file| 
 10       write_file.write "CREATE PROCEDURE [dbo].[#{match[0]}]\n" 
 11       write_file.write match[1] 
 12     end 
 13   end 
 15 end 
Code syntax highlighting by VIM captured with ScreenShot script
Published Jul 24 2007, 06:06 PM by chrisortman
Filed under:


No Comments

Leave a Comment

Powered by Community Server (Commercial Edition), by Telligent Systems