Skip to content

Instantly share code, notes, and snippets.

@SQLKiwi

SQLKiwi/demo.sql Secret

Created Aug 26, 2021
Embed
What would you like to do?
-- 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