1

I have 1500 odd T-SQL scripts which are formatted as follows:

DROP PROCEDURE <stored proc name>

CREATE PROCEDURE 
<stored proc definition>

I've been tasked to insert an IF EXISTS condition on all of them, before they try to drop the procedure, so the scripts won't fail the first time they run.

I'm looking for an efficient way (in a windows 7 environment) to modify all these scripts to add the following condition before the drop statement. I'm not too keen on modifying them manually!

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'<stored proc name>') AND type in (N'P', N'PC'))

EDIT: Sublime text editor with Regex seems to be a good tool to use. I've never used Regex before but I've figured out that 

(?<=DROP PROCEDURE ).*

Will find everything on the line after "stored procedure". I want to put this in the Replace statement but it doesn't seem to be reading it (the actual regex is just inserted into the code). How can I do this?

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
yaziyo
  • 11
  • 2
  • 1
    You could use a text editor, such as sublime, and use regex to do a search and replace. Is this something you've tried already? – Shafiq Jetha Dec 19 '13 at 00:44
  • How are they stored presently? Excel is handy for creating queries. – Hart CO Dec 19 '13 at 00:48
  • @ShafiqJetha I haven't used that application before. I'll give it a try and see if I can batch edit. – yaziyo Dec 19 '13 at 00:52
  • @GoatCO They're just a bunch of sql text files stored on a local drive. – yaziyo Dec 19 '13 at 00:53
  • Either a text editor as was suggested, or you could write a little console app to do it (or a python script, if you know python) – valverij Dec 23 '13 at 19:53

1 Answers1

0

@Shafiq Jetha renders good advice. Give Sublime Text a whirl:

  1. Download Sublime Text from here: http://www.sublimetext.com/3
  2. In Windows Explorer, select all your .sql file > opposite click > Open With > Sublime Text
  3. Use a Regular Expressions and Search and Replace - Multiple Files

This post can help with the Search and Replace - Multiple Files

This can help with the Regular Expression

Community
  • 1
  • 1
Jon Jaussi
  • 1,298
  • 3
  • 18
  • 36