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' 
  3 
  4 File.open('storedprocs.sql','r') do |file| 
  5    
  6   text = file.read 
  7 
  8   text.scan(/CREATE PROCEDURE \[dbo\]\.\[(API_\w+)\]\s*(.*?)'\s*^END$\s*^GO$/m) do |match| 
  9     File.open("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 
 14 
 15 end