regular expressions on sql file

Question

I need to perform a search and replace operation on hundreds of SQL script files. The idea is to transform this:

CREATE PROCEDURE [dbo].[proc_whatever]
    (
      @id INT,
      @parameter VARCHAR(1)
    )
AS 
    BEGIN
        ...
    END

Into this:

IF OBJECT_ID('[dbo].[proc_whatever]') IS NULL
      EXEC('CREATE PROCEDURE [dbo].[proc_whatever] AS SET NOCOUNT ON;')
GO

ALTER PROCEDURE [dbo].[proc_whatever]
    (
      @id INT,
      @parameter VARCHAR(1)
    )
AS 
    BEGIN
        ...
    END

To do this I would use Notepad++.

Any tip or ideas?

thanks to all those who answered. i really appreciate your help — ab_732, Apr 26 '15 at 19:23

dovid · Answer 1 · 2015-04-26T18:34:19.163

1

search:

CREATE PROCEDURE ([\[\]a-zA-Z_.]+)\s*\(

replace in:

IF OBJECT_ID \('$1'\) IS NULL \n EXEC \('CREATE PROCEDURE $1  AS SET NOCOUNT ON;'\) \n GO \n ALTER PROCEDURE $1 \(

edited Apr 26 '15 at 18:34

answered Apr 26 '15 at 18:23

dovid

6,354
3
33
73

score 1 · Accepted Answer · answered Apr 26 '15 at 18:32

You can use this regex replacement:

Find What:     CREATE\s+PROCEDURE\s+(\[.*?\]\.\[.*?\])\s+\(\s+(@id INT,\s+@parameter VARCHAR\(\d+\)\s+)\s+\)
Replace With:  IF OBJECT_ID\('$1'\) IS NULL\r\n    EXEC\('CREATE PROCEDURE $1 AS SET NOCOUNT ON;'\)\r\nGO\r\n\r\nALTER PROCEDURE $1\r\n    \(\r\n      $2\)

Tested:

enter image description here

regular expressions on sql file

2 Answers2