December 30, 2020 at 7:35 pm
Hi all,
If you had the string
"CREATE VIEW <something> AS SELECT * FROM <sometable>;"
How would you extract just the "SELECT * FROM <sometable>;" piece?
I know you're probably thinking, "Uh, this is pretty fundamental...just use SUBSTRING with CHARINDEX". Maybe something like:
DECLARE @sql AS VARCHAR(100) = 'CREATE VIEW <something> AS SELECT * FROM <sometable>;';
SELECT @sql AS OriginalStatmeent, SUBSTRING(@sql, CHARINDEX('SELECT', @sql), LEN(@sql)) AS SelectStatement;
but what if the view name itself contains "SELECT" or "AS"? The query would fail.
DECLARE @sql AS VARCHAR(100) = 'CREATE VIEW [SELECT_BEST_ASSET] AS SELECT * FROM <sometable>;';
SELECT @sql AS OriginalStatmeent, SUBSTRING(@sql, CHARINDEX('SELECT', @sql), LEN(@sql)) AS SelectStatement;
SELECT @sql AS OriginalStatmeent, SUBSTRING(@sql, CHARINDEX('AS', @sql), LEN(@sql)) AS SelectStatement;
Then, you might think to look for a space before and/or after the "AS" as a way to substring, but there's no guarantee that there would only be one. It could be
"CREATE VIEW [SELECT_BEST_ASSET] AS SELECT..."
or it could be that there's a carriage return or line feed before or after the "AS".
Maybe I'm missing something obvious but how would you extract the entire SELECT statement?
Note: I am trying to apply this to several view definitions, and the view names are of varying lengths of course. The definition could also show them as being written with or without brackets, with or without a schema specified, etc.--which is why I haven't tried to substring on that portion of the view creation structure.
Thank you in advance.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
December 30, 2020 at 9:00 pm
I don't think you are going to have a nice "one size fits all" solution here. You are probably going to need to be creative and do multiple SUBSTRINGs or REPLACEs or something if you intend to keep the formatting intact.
BUT assuming you don't need to keep the formatting the same, I'd start by replacing all new line (carriage return and line feed) characters with a space (ie ' '). Now your CREATE VIEW statement, when PRINTed should be a single line. I recommend a space rather than nothing simply because otherwise you may end up with something like "ASSELECT". Next, I'd replace all tabs with a single space. At this point, searching for CHARINDEX of 'AS ' should work almost every time.
So to re-iterate, something like:
SELECT @sql AS OriginalStatmeent, LTRIM(SUBSTRING(REPLACE(REPLACE(REPLACE(@sql,CHAR(13),' '),CHAR(10),' '),'',' '), CHARINDEX('AS ', REPLACE(REPLACE(REPLACE(@sql,CHAR(13),' '),CHAR(10),' '),'',' '))+3, LEN(REPLACE(REPLACE(REPLACE(@sql,CHAR(13),' '),CHAR(10),' '),'',' ')))) AS SelectStatement;NOTE - I only partially tested the above. There may be cases where it doesn't work well. You will probably get better performance by running that REPLACE logic BEFORE the SUBSTRING directly against the @sql variable. IE something like:
SELECT @sql = REPLACE(REPLACE(REPLACE(@sql,CHAR(13),' '),CHAR(10),' '),'',' ')
and then using one of your original queries rather than running that replace logic on the variable 3 times.
EDIT - if you need to keep source formatting it gets a bit more difficult, but still not impossible. Just instead of replacing everything with spaces, replace it with a space, a character or string you know isn't in use and another space. So for example with CHAR(10), you could replace that with ' CHAR(10) '. Then once you finish the substring, do replaces again so ' CHAR(10) ' becomes CHAR(10) again.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 30, 2020 at 9:20 pm
Brian,
Thank you for commenting! I hadn't thought about proactively REPLACE-ing CHAR 13 and 10 with a single space to simplify things and then also be able to use LTRIM and RTRIM to trim where necessary.
Although retaining the actual structure isn't necessary, I see what you mean by replacing with something not in use. If that becomes a requirement, I'll use that! I'm actually a big fan of the pencil...... SELECT NCHAR(9999);
I'm going to analyze this a bit further. It looks promising, so thank you again!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
December 31, 2020 at 4:01 am
After the other REPLACEs, you might as well replace all multiple spaces with a single space, to avoid any possible issues with, for example, " AS ". The actual REPLACEs would be something like this:
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(<your_string_here>,
SPACE(65), SPACE(1)), SPACE(33), SPACE(1)), SPACE(17), SPACE(1)), SPACE(9), SPACE(1)),
SPACE(5), SPACE(1)), SPACE(3), SPACE(1)), SPACE(2), SPACE(1)), SPACE(2), SPACE(1))
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 31, 2020 at 12:01 pm
Scott,
Thanks for the response. Would you mind explaining your code a little bit?
I get the gist that you're replacing multiple spaces with one, but why the variance of #s in the SPACE() function? Like Why SPACE(65), 33, 9, 2, etc.? Are you suggesting I should have a REPLACE for every potential # of spaces, all that replace with 1 space?
Not the exact code, but something like:
REPLACE(<string>, SPACE(2), SPACE(1))
REPLACE(<previous string>, SPACE(3), SPACE(1))
REPLACE(<previous string>, SPACE(4), SPACE(1))
REPLACE(<previous string>, SPACE(N), SPACE(1))
?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
December 31, 2020 at 3:23 pm
No, the specific values are essentially the REPLACE equivalent of a binary search.
The initial code came from someone else. It performs really well overall, better than other methods, so I suggested it, given that the volume of data will be potentially very large when you're looking at SQL code.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 31, 2020 at 3:28 pm
One thing to be cautious of that I didn't think of until today (just now) - AFTER getting the "final" string, you may want to check for a WHERE clause and for JOINs. If these have comparisons onto literal strings (such as 'hello world'), you MAY want to check to make sure that they didn't have new lines in them or tabs as both could result in your final query breaking.
As for SPACE, you run into the same snag. If the JOIN/WHERE condition has multiple spaces, you may break the JOIN/WHERE when removing spaces.
Was thinking about preserving the style of the query as well by putting in the literal string 'CHAR(10)' in place of the character CHAR(10) and you run into snags then when doing your substring because you could have 'ASCHAR(10)SELECT' and when you search for ' AS ' it won't show up. So you would need to do multiple different checks...
This may not be a problem for you, but it is something I would look into just in case.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 31, 2020 at 5:20 pm
Hey all, I'm wondering if I can avoid using REPLACE at all (at least with spaces and/or CHAR(10) CHAR(13) so that we don't need to worry about potential JOIN predicate or WHERE clause string literals.
What do you both think about the following code? Would this account for the situations you've mentioned, both in terms of watching out for specific characters + preserving the structure of the defition?
WITH vCTE AS (
SELECT t.ViewID, t.ViewDefinition, n.Number, RANK() OVER(PARTITION BY t.ViewID ORDER BY n.Number) AS RNK
FROM (VALUES
(1, 'CREATE VIEW [SELECT_BEST_ASSET] AS SELECT * FROM <sometable> WHERE something = ''THE NAME AS WE KNOW IT'';'),
(2, 'CREATE VIEW [SELECT_BEST_ASSET]' + CHAR(13) + CHAR(10) + 'AS SELECT * FROM <sometable> WHERE something = ''THE NAME AS WE KNOW IT'';'),
(3, 'CREATE VIEW [SELECT_BEST_ASSET]' + CHAR(13) + CHAR(10) + 'AS /*some comments*/ SELECT * FROM <sometable> WHERE something = ''THE NAME AS WE KNOW IT'';'),
(4, 'CREATE VIEW [SELECT_BEST_ASSET]' + CHAR(13) + CHAR(10) + 'AS' + CHAR(13) + CHAR(10) + '--comment' + CHAR(13) + CHAR(10) + 'SELECT * FROM <sometable> WHERE something = ''THE NAME AS WE KNOW IT'';')
) t (ViewID, ViewDefinition)
CROSS APPLY (SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS Number FROM sys.all_objects) n
WHERE n.Number > 1
AND n.Number <= CONVERT(INT, LEN(t.ViewDefinition) + 1)
AND (SUBSTRING(' AS' + t.ViewDefinition, n.Number, LEN(' AS')) = ' AS'
OR SUBSTRING(CHAR(13) + CHAR(10) + 'AS' + t.ViewDefinition, n.Number, LEN(CHAR(13) + CHAR(10) + 'AS')) = CHAR(13) + CHAR(10) + 'AS')
)
SELECT ViewID, ViewDefinition, SUBSTRING(ViewDefinition, Number, LEN(ViewDefinition)) AS SelectStmt
FROM vCTE
WHERE RNK = 1
ORDER BY ViewID;
Mike Scalise, PMP
https://www.michaelscalise.com
December 31, 2020 at 10:55 pm
you didn't specify where you will be getting the text for the views - that may also change how you process it.
but take the following examples in consideration - they will through you out of your way with any type of charindex processing
create view
/* view name: dbo.myview
date created: 2020-12-31
*/
dbo.myview as
select *
from ProdTable
/* view name dbo.myview
date created: 2020-12-31
*/
create view
dbo.myview2 as
select *
from ProdTable
create view
[dbo] . [myview3]
as
select *
from ProdTable
January 1, 2021 at 1:06 am
frederico,
Thank you for the response. Have you looked at my most recent proposed code? It doesn't use CHARINDEX, so I believe it works in all of your examples...
Mike Scalise, PMP
https://www.michaelscalise.com
January 1, 2021 at 9:55 am
Hi,
Had not seen it when I hit the send button.
your code works for most - not all cases.
see these two examples
create view dbo.myview5
/* view name dbo.myview
--*/as
select *
from ProdTable
create view dbo.myview6
/* view name dbo.myview defined as a way to test
--*/
as
select *
from ProdTable
And with regards to your other examples... obviously a matter of opinion but I would not consider the following output of your code as "valid" as it contains the comments before the SELECT - at least not without also including the remaining comments that are before the " AS"
/*some comments*/ SELECT * FROM <sometable> WHERE something = 'THE NAME AS WE KNOW IT';
--comment SELECT * FROM <sometable> WHERE something = 'THE NAME AS WE KNOW IT';
January 2, 2021 at 5:13 pm
Fair enough, but what if I remove all of the comments beforehand, then employ my technique? Then it should handle your most recent examples AND be considered valid since it wouldn't contain any comments...
Something like....
WITH AllViewsCTE
AS (SELECT ROW_NUMBER() OVER(ORDER BY s.[name], v.[name]) AS ViewNumber,
s.[name] + '.' + v.[name] AS ViewName,
OBJECT_DEFINITION(OBJECT_ID(v.[name])) AS ViewDefinition
FROM sys.views v
INNER JOIN sys.schemas s
ON v.schema_id = s.schema_id),
CommentsCTE
AS (SELECT av.ViewNumber,
av.ViewName,
av.ViewDefinition,
CASE
WHEN SUBSTRING('/*' + av.ViewDefinition, Number, LEN('/*')) = '/*' THEN 'M'
WHEN SUBSTRING('--' + av.ViewDefinition, Number, LEN('--')) = '--' THEN 'S'
ENDAS CommentType,
n.Number - 2AS StartPosCom,
CHARINDEX(CASE
WHEN SUBSTRING('/*' + av.ViewDefinition, n.Number, LEN('/*')) = '/*' THEN '*/'
WHEN SUBSTRING('--' + av.ViewDefinition, n.Number, LEN('--')) = '--' THEN CHAR(13) + CHAR(10)
END, av.ViewDefinition, n.Number)AS EndPosCom
FROM AllViewsCTE av
CROSS APPLY (SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS Number
FROM sys.all_objects) n
WHERE n.Number > 1
AND n.Number <= CONVERT(INT, LEN(av.ViewDefinition) + 1)
AND ( SUBSTRING('/*' + av.ViewDefinition, n.Number, LEN('/*')) = '/*'
OR SUBSTRING('--' + av.ViewDefinition, n.Number, LEN('--')) = '--' )),
CommentsCondensedCTE
AS (SELECT *,
RANK() OVER(PARTITION BY cc.ViewNumber ORDER BY cc.StartPosCom, cc.EndPosCom) AS Rnk
FROM (SELECT *
FROM CommentsCTE c2
WHERE c2.CommentType = 'S'
AND NOT EXISTS (SELECT 1
FROM CommentsCTE c1
WHERE c1.CommentType = 'M'
AND c2.StartPosCom BETWEEN c1.StartPosCom AND c1.EndPosCom
AND c1.ViewNumber = c2.ViewNumber)
UNION
SELECT *
FROM CommentsCTE c1
WHERE c1.CommentType = 'M'
AND NOT EXISTS (SELECT 1
FROM CommentsCTE c2
WHERE c2.CommentType = 'S'
AND c1.StartPosCom BETWEEN c2.StartPosCom AND c2.EndPosCom
AND c1.EndPosCom BETWEEN c2.StartPosCom AND c2.EndPosCom
AND c1.ViewNumber = c2.ViewNumber)) cc),
NonCommentSUBSTRINGPositionsCTE
AS (SELECT COALESCE(cc1.ViewNumber, cc2.ViewNumber) AS ViewNumber,
COALESCE(cc1.ViewName, cc2.ViewName) AS ViewName,
COALESCE(cc1.ViewDefinition, cc2.ViewDefinition) AS ViewDefinition,
ISNULL(cc2.EndPosCom + 2, 1) AS StartPos,
cc1.StartPosCom AS EndPos,
SUBSTRING(COALESCE(cc1.ViewDefinition, cc2.ViewDefinition), ISNULL(cc2.EndPosCom + 2, 1), ISNULL(cc1.StartPosCom, LEN(cc2.ViewDefinition) + 1) - ISNULL(cc2.EndPosCom + 2, 1)) AS ViewDefNoComments
FROM CommentsCondensedCTE cc1
FULL JOIN CommentsCondensedCTE cc2
ON cc1.ViewNumber = cc2.ViewNumber
AND cc1.Rnk - 1 = cc2.Rnk
WHERE ISNULL(cc1.StartPosCom, LEN(cc2.ViewDefinition) + 1) - ISNULL(cc2.EndPosCom + 2, 1) > 0),
NonCommentSUBSTRINGsCondensedCTE
AS (SELECT ncsp1.ViewNumber,
ncsp1.ViewName,
ViewDefinition = STUFF((SELECT CHAR(13) + CHAR(10) + ncsp2.ViewDefNoComments
FROM NonCommentSUBSTRINGPositionsCTE AS ncsp2
WHERE ncsp2.ViewNumber = ncsp1.ViewNumber
ORDER BY ncsp2.ViewNumber,
ncsp2.StartPos,
ncsp2.EndPos
FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'')
FROM NonCommentSUBSTRINGPositionsCTE AS ncsp1
GROUP BY ncsp1.ViewNumber,
ncsp1.ViewName),
AllViewsRevisedCTE
AS (SELECT COALESCE(ncsc.ViewNumber, av.ViewNumber) AS ViewNumber,
COALESCE(ncsc.ViewName, av.ViewName) AS ViewName,
COALESCE(ncsc.ViewDefinition, av.ViewDefinition) AS ViewDefinition
FROM NonCommentSUBSTRINGsCondensedCTE ncsc
FULL JOIN AllViewsCTE av
ON ncsc.ViewNumber = av.ViewNumber),
ViewQueryKeywordsCTE
AS (SELECT avr.ViewNumber,
avr.ViewName,
avr.ViewDefinition,
n.Number,
RANK() OVER(PARTITION BY avr.ViewNumber ORDER BY n.Number) AS Rnk
FROM AllViewsRevisedCTE avr
CROSS APPLY (SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS Number
FROM sys.all_objects) n
WHERE n.Number > 1
AND n.Number <= CONVERT(INT, LEN(avr.ViewDefinition) + 1)
AND ( SUBSTRING(' AS' + avr.ViewDefinition, n.Number, LEN(' AS')) = ' AS'
OR SUBSTRING(CHAR(13) + CHAR(10) + 'AS' + avr.ViewDefinition, n.Number, LEN(CHAR(13) + CHAR(10) + 'AS')) = CHAR(13) + CHAR(10) + 'AS' ))
SELECT vqk.ViewNumber,
vqk.ViewName,
vqk.ViewDefinition,
SUBSTRING(vqk.ViewDefinition, vqk.Number, LEN(vqk.ViewDefinition)) AS QueryFromView
FROM ViewQueryKeywordsCTE vqk
WHERE vqk.Rnk = 1
ORDER BY vqk.ViewNumber;
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply