SQL Server meta-development (with regular expressions and more)

Yesterday, I had about 30 existing queries (and some multi-query processes) that I needed to turn into individual stored procedures. I could, of course, go through the entire list of queries manually, crafting CREATE PROC statements and so on. But what’s the fun in that?

As much as possible, I like to use scripts on my scripts, use regular expressions, and batch actions…even my own.

Batch development

Let’s use this simple script as an example:

Note: For my particular application, I chose to modify the script so that I could separate the batches and insert each one into a temporary table, for further manipulation. If you don’t need to do additional manipulation, you could skip adding them to a table, and instead, just make your CREATE PROC scripts in the file.

My IRL “batch” process for working on this task went something like this:

Step 1: Deal with single quotes

Replace all single quotes with two single quotes. We’re going to turn the entire script into a string, so we must handle the single quotes before doing anything else.

Step 2: Separate query sets

Identify anything that consistently differentiates one query (or one group of queries) from the next. In my script, I was lucky enough to have a comment and special identifier at the start of each separate group, something like “– XYZid …”.


Use replace all with regular expressions (using that differentiator )to surround each query set with single quotes, turning each one into a UNION SELECT + string statement. In this case, I used

replace — XZYid

with ‘\n\nUNION SELECT ‘– XYZid

Note that \n is a regular expression for “newline”. That let us put a couple of lines between the (new) end quote after each batch, and the (new) UNION SELECT + start quote before each batch.

Step 3: Quick adjustments

Change the first statement into a SELECT (not UNION SELECT). Now, we have a valid SELECT…UNION SELECT statement.

Step 4: CREATE PROCEDURE

We’re going to create a table and insert all these strings into it. But first, let’s go ahead and make the statements into CREATE PROCEDURE statements.

Replace — XYZid

With — XYZid \n CREATE PROCEDURE Minion.
This isn’t perfect, because we don’t have our “AS” keyword after the procedure name. Normally, I’d use regex tagged expressions to deal with this, but SSMS is being persnickity about tagged expressions right now. Grrr. We’ll have to deal with the “AS” later, maybe manually.

Step 5: Insert into a table, continue

Now we’ll just create a temporary table and insert all these lovely strings into it!

CREATE TABLE #temp (strings varchar(max) NULL);

INSERT INTO #temp (strings)
SELECT….
UNION SELECT ….

With each separate proto-procedure in its own row, I can do all kinds of cool stuff. Not least of which is – oh hey, look at that! – figuring out the procedure name

select
	/* Where in the string is "create procedure"? */
	  charindex('create procedure', strings)

	/* everything to the left of (and including) "CREATE PROCEDURE" */
	, LEFT(strings, charindex('create procedure', strings)+16)  as [Left, with Proc]
	
	/* everything to the right of (and NOT including) "CREATE PROCEDURE" */
	, RIGHT (strings, len(strings)-charindex('create procedure', strings)-16) as [Right of Proc] 
	
	/* Pull out the procedure name based on "CREATE PROCEDURE" as the start, and the first space after the next string as the end. */
	, SUBSTRING(strings
		, charindex('create procedure', strings)+17			-- <--starting place
		, CHARINDEX(' ', RIGHT (strings, len(strings)-charindex('create procedure', strings)-16))	-- <--ending place
		)
from #temp;

Once we figured that out, we can pull that name out and edit the CREATE PROC statements:

UPDATE #temp 
SET ProcName = SUBSTRING(strings
		, CHARINDEX('create procedure', strings)+17			-- starting place
		, CHARINDEX(' ', RIGHT (strings, len(strings)-charindex('create procedure', strings)-16))	-- ending place
		);

UPDATE #temp SET ProcName = LTRIM(RTRIM(ProcName));

/* Add "AS" after the procedure name */
UPDATE #temp 
SET strings = REPLACE(strings
	, 'CREATE PROCEDURE ' + ProcName
	, 'CREATE PROCEDURE ' + ProcName + ' AS ');

Step 6: Use a cursor to print them all out

We could use PowerShell to pull out all those lovely edited scripts, or we could use a cursor and PRINT statements. There are plusses and minuses to each, but today we’re in a hurry, so:

DECLARE @SP VARCHAR(MAX);

DECLARE crs CURSOR FOR 
SELECT strings FROM #temp 
ORDER BY ProcName; 

OPEN crs;

FETCH NEXT FROM crs INTO @SP;

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @SP;
	PRINT 'GO';
	PRINT '';
	FETCH NEXT FROM crs INTO @SP;
END;

CLOSE crs;
DEALLOCATE crs;

Yes, it looks like a lot of work. But – especially considering the other fiddly things I had to do with the actual scripts I worked on – it’s far, far less work to semi-automate (or semi-batch, or meta-develop) the scripts with methods like these.


By the way, the queries in the examples – SELECT * FROM Minion.IndexSettingsDB, and so on – come from our free-and-outstanding SQL Server backup and maintenance scripts. Download, install, and configure to your heart’s content. They’re feature-rich….Availability Group-aware, to name just one!