-
-
Save SQLKiwi/9cee90ed4ed720432e12fe7368f58abc to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Start timer | |
DECLARE @start datetime2 = SYSUTCDATETIME(); | |
-- No plans or row count messages | |
SET STATISTICS XML OFF; | |
SET NOCOUNT ON; | |
--- Get the source code | |
DECLARE @source nvarchar(max)=( | |
SELECT REPLACE([definition], NCHAR(13)+NCHAR(10), NCHAR(13)) | |
FROM sys.sql_modules | |
WHERE [object_id]=OBJECT_ID('dbo.sp_Blitz')); | |
-- Text to find | |
DECLARE | |
@find nvarchar(128) = N'test'; | |
-- Working variables | |
DECLARE | |
@find_len bigint = LEN(@find), | |
@line_num bigint = 0, | |
@line_start bigint = 0, | |
@line_end bigint = 0, | |
@match_pos bigint = 0; | |
-- Lines with matches | |
DECLARE @MatchLines table ( | |
line bigint PRIMARY KEY, | |
line_start bigint NOT NULL, | |
line_length bigint NOT NULL); | |
-- Find first match position | |
SET @match_pos = CHARINDEX(@find, @source); | |
WHILE @match_pos > 0 | |
BEGIN | |
-- Count lines and find start/end points | |
WHILE @line_end < @match_pos | |
BEGIN | |
SELECT | |
@line_start = @line_end + 1, | |
@line_end = CHARINDEX( | |
NCHAR(13), | |
@source COLLATE Latin1_General_100_BIN2, | |
@line_start), | |
@line_num += 1; | |
-- No more lines | |
IF @line_end = 0 SET @line_end = 2147483647; | |
END; | |
-- Record information about this match line | |
INSERT @MatchLines | |
(line, line_start, line_length) | |
VALUES | |
(@line_num, @line_start, @line_end - @line_start); | |
-- Find next match | |
SET @match_pos = | |
CHARINDEX(@find, @source, @match_pos + @find_len); | |
END; | |
-- Show matched lines | |
SELECT | |
ML.line, | |
line_text = | |
SUBSTRING( | |
@source COLLATE Latin1_General_100_BIN2, | |
ML.line_start, | |
ML.line_length) | |
FROM @MatchLines AS ML | |
ORDER BY ML.line ASC; | |
SELECT DATEDIFF(MILLISECOND, @start, SYSUTCDATETIME()); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment