Extracting SELECT statement from queries.

Question

Post reply

Extracting SELECT statement from queries.

Mike Scalise

SSCrazy Eights

Points: 8145
More actions
December 22, 2020 at 2:36 pm

#3823200
Hi All,
I have a need to replace, in various views, the SELECT statement that produces the results (SELECT --> SELECT TOP 10). These queries could be in any of the following formats:
```
--1) single select
SELECT *
FROM test

--2) CTE with single select
WITH test 
     AS (SELECT 1 a), 
     test2 
     AS (SELECT 2 b)
SELECT * 
FROM   test 

--3) Multiple selects with union
SELECT * 
FROM   test 
UNION ALL 
SELECT * 
FROM   test2 

--4) Multiple selects with union all and with outer select
SELECT *
FROM (
SELECT * 
FROM   test 
UNION ALL 
SELECT * 
FROM   test2 
) t


--5) CTE with multiple selects (with union all)
WITH test 
     AS (SELECT 1 a), 
     test2 
     AS (SELECT 2 b)
SELECT * 
FROM   test 
UNION ALL 
SELECT * 
FROM   test2
```
In cases, like #s 1 and 4, I can try to find the first occurrence of a SELECT (that's not a comment). In some other cases, like #2, I can try to find the last occurrence of a SELECT (that's not a comment). However, in cases like #s 3 and 5, I'm having to contend with UNION ALLs, and would want ALL of the SELECTs involved in the UNION ALL to be replaced with SELECT TOP 10.
I realize this is a little complicated, but I don't think it's impossible. I'm trying to talk it through in English to see how I might approach this to accommodate all of these cases, but I could use some help if anyone has any suggestions.
Thanks in advance!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

I presume you've got a lot of this to do & you want to build an automated way of doing it.

The way I've approached tasks like this in the past is something like this:

Use source control along with local versions of your database(s) and database DDL scripts.
Create a utility which performs the replaces on the .sql script files. I would do this in C#, but any sort of script which can modify the .sql files and search using Regular Expressions should be sufficient.
Run the update on the .sql files
Spot check your .sql files to make sure that the update is performing as required. If not, use your VCS to discard all the updates and then modify your update routine accordingly.
When your spot checks are looking good, run a build in SSDT, using the modified .sql files, to see whether your update worked. If it works, try deploying to your local DB to see whether it works as intended.
Make the changes incrementally. It should be a quick process to update / check / discard the files, as no database updates are required until the changes are looking good.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 2

Phil Parkin wrote:

I presume you've got a lot of this to do & you want to build an automated way of doing it.

Correct. I have quite a few.

Phil Parkin wrote:

I would do this in C#, but any sort of script which can modify the .sql files and search using Regular Expressions should be sufficient.

I am actually looking to do this in T-SQL, if possible. I know, it would be much easier with a robust language like C# that support regex, but I am trying to keep it all in T-SQL.

I'm just curious--given these restrictions, any other thoughts?

Mike

Mike Scalise, PMP
https://www.michaelscalise.com