1 SET ANSI_NULLS ON; 2 SET ANSI_PADDING ON; 3 SET ANSI_WARNINGS ON; 4 SET CONCAT_NULL_YIELDS_NULL ON; 5 SET NUMERIC_ROUNDABORT OFF; 6 SET QUOTED_IDENTIFIER ON; 7 8 IF OBJECT_ID(‘[dbo].[FN_SPLIT_INT]‘) IS NOT NULL 9 DROP FUNCTION [dbo].[FN_SPLIT_INT] 10 GO 11 12 IF OBJECT_ID(‘[dbo].[FN_SPLIT]‘) IS NOT NULL 13 DROP FUNCTION [dbo].[FN_SPLIT] 14 GO 15 16 CREATE FUNCTION [dbo].[FN_SPLIT] ( 17 @Text nvarchar(max), -- Text to split 18 @Delimiter nvarchar(1000) -- Value to split on, try to only pass a single char. See notes for details. 19 ) 20 /* ------------------------------------------------------------------------------------------------ 21 Purpose: Split text on a common char. 22 Design Notes: 23 1) Will trim leading/trailing white space from items. 24 2) Will omit blank and null items. 25 3) Compatible from SQL 2005 and onward (see details about [item_int] in return table) 26 4) Return table item is nvarchar(max) (not bound by string length) 27 5) Reasonably durable with escape sequences, so if a delimiter has a [,%,_ in it, the patter should still match. 28 6) Uses a sliding search window, not saving the remaining text on each iteration. However, 29 saving each item in a temp variable (@item) was faster than using a CTE to temporarily 30 store the value, which surprised me. 31 7) Returns the value as an int as well, which is a common use for this function (splitting comma 32 separated lists of ints). Note that this should be low impact in that if you don‘t query 33 against that column since it is a non-persistent computed column (i.e. low overhead). 34 8) Supports @Delimiter > 1 char, but slower. Note in the unit tests, with text of approximately 35 10K, 1 char is about 30% faster, hence the big IF block in the code. Plus, the multi-char 36 delimiter does not scale terribly well. The test with 100,000 records, a 1 char delimiter takes 37 about 6 seconds while with a 5 char delimiter took 430 seconds (7 minutes!). As such, try to 38 replace your multi char delimiters with a single char value before calling this function. 39 Side Note: For what it‘s worth, I did try an alternative method of saving the remaining 40 "working text" as a sub string of text so the search would get faster near the end, but overall 41 it was slower at about 500 seconds. 42 43 NOTE: This version does not support case sensitivity. See "TODO" comments if you need a case insensitive version 44 45 Revision history: 46 --------------------------------------------------------------------------------------------------- 47 Date User Change Description 48 --------------------------------------------------------------------------------------------------- 49 10/10/2013 Brad Joss (bradjoss@hotmail.com) Initial Public Draft 50 ---------------------------------------------------------------------------------------------------*/ 51 RETURNS @retTable TABLE 52 ( 53 -- Output table definition 54 [item] nvarchar(max) COLLATE DATABASE_DEFAULT NOT NULL, 55 56 -- Since most of the times we split, we are splitting lists of ints, this makes that process easier. 57 -- Since the column is non persistent it should only be evaluated when requested (low overhead). 58 [item_int] as ( 59 -- SQL 2012 version, better, use if possible 60 -- TRY_CONVERT([int], NULLIF([item],‘‘)) -- SQL 2012 Format, faster and safer, but pre-2012 code provided as well... 61 62 -- Pre SQL 2012 syntax. Not as reliable, so use 2012 when possible by commenting out this CAST and using the TRY_CONVERT above 63 CAST( 64 CASE 65 WHEN LEN(item) > 11 THEN NULL -- LEN OF (-2147483648) is 11. Simple out of bounds checking. 66 WHEN ISNUMERIC([item]) = 1 AND [item] NOT LIKE ‘%.%‘ THEN [item] -- Ensure value conforms to int 67 ELSE null 68 END 69 as int) 70 ) 71 ) 72 WITH SCHEMABINDING 73 AS 74 BEGIN 75 -- Garbage in, Garbage out. If they did not pass input data, return nothing. 76 IF RTRIM(ISNULL(@Text,‘‘)) = ‘‘ OR RTRIM(ISNULL(@Delimiter,‘‘)) = ‘‘ 77 RETURN 78 79 DECLARE 80 @ix bigint -- Current index 81 , @pix bigint -- Previous index 82 , @del_len int -- Delimiter length 83 , @text_len bigint -- Input text length 84 , @item nvarchar(max) -- Temp item buffer. I tried w/o using CTEs, but this way was faster 85 86 SELECT @del_len = LEN(@Delimiter) 87 , @text_len = LEN(@Text) 88 89 IF @del_len = 1 90 BEGIN -- CHARINDEX MODE (Much faster than PATINDEX mode) 91 SELECT @ix = CHARINDEX(@Delimiter, @Text) -- TODO: If you want to implment Case Insensitivity here, wrap both in LOWER() 92 , @pix = 0 93 94 -- No delim found, just return the passed value, trimmed 95 IF @ix = 0 96 BEGIN 97 INSERT INTO @retTable(item) 98 SELECT LTRIM(RTRIM(@Text)) -- We know this is not null because of the first GIGO check above 99 END 100 ELSE 101 BEGIN 102 -- Find most of the matches 103 WHILE @ix > 0 104 BEGIN 105 SELECT 106 -- Get the current value 107 @item = LTRIM(RTRIM(SUBSTRING(@Text,@pix,(@ix - @pix)))) 108 -- Move previous pointer to end of last found delimiter 109 , @pix = @ix + @del_len 110 -- And update the values for next pass though the loop, finding the next match 111 , @ix = CHARINDEX(@Delimiter, @Text, (@ix + @del_len)) -- TODO: If you want to implment Case Insensitivity here, wrap both in LOWER() 112 113 IF @item <> ‘‘ AND @item IS NOT NULL -- Only save non empty values 114 INSERT INTO @retTable(item) VALUES (@item) 115 END 116 117 -- Get the trailing text 118 SET @item = LTRIM(RTRIM(SUBSTRING(@Text,@pix,@text_len))) 119 IF @item <> ‘‘ AND @item IS NOT NULL -- Only save non empty values 120 INSERT INTO @retTable(item) VALUES (@item) 121 END -- @ix = 0 122 END 123 ELSE -- @del_len = 1 124 BEGIN -- PATINDEX Mode (SLOW!) Try to pass in text that uses single char delimeters when possible 125 126 DECLARE @del_pat nvarchar(3002) -- Assume 3x @Delimiter + 2, for escaping every character plus wrapper % 127 128 -- Escape characters that will mess up the like clause, and wrap in wild cards % 129 SELECT @del_pat = ‘%‘ + REPLACE(REPLACE(REPLACE(@Delimiter 130 , ‘[‘,‘[[]‘) 131 , ‘%‘,‘[%]‘) 132 , ‘_‘, ‘[_]‘) 133 + ‘%‘ 134 135 SELECT @ix = PATINDEX(@del_pat, @Text) -- TODO: If you want to implment Case Insensitivity here, wrap both in LOWER() 136 , @pix = 0 137 138 -- No delim found, just return the passed value, trimmed 139 IF @ix = 0 140 BEGIN 141 INSERT INTO @retTable(item) 142 SELECT LTRIM(RTRIM(@Text)) -- We know this is not null because of the first GIGO check above 143 END 144 ELSE 145 BEGIN 146 -- Find most of the matches 147 WHILE @ix > 0 148 BEGIN 149 SELECT 150 -- Get the curent Item 151 @item = LTRIM(RTRIM(SUBSTRING(@Text,@pix,(@ix - @pix)))) 152 -- Move the previous index to the end of the previous delimiter 153 , @pix = @ix + @del_len 154 -- And set values for next itteration of the loop, finding the next match 155 , @ix = PATINDEX(@del_pat, SUBSTRING(@Text, (@ix + @del_len), @text_len)) -- TODO: If you want to implment Case Insensitivity here, wrap both in LOWER() 156 157 IF @item <> ‘‘ AND @item IS NOT NULL -- Only save non empty values 158 INSERT INTO @retTable(item) VALUES (@item) 159 160 IF @ix > 0 SET @ix = ((@ix + @pix) - 1) -- -1 since PatIndex is 1 based and Substring is 0 based 161 END 162 163 -- Get the trailing text 164 SET @item = LTRIM(RTRIM(SUBSTRING(@Text,@pix,@text_len))) 165 IF @item <> ‘‘ AND @item IS NOT NULL -- Only save non empty values 166 INSERT INTO @retTable(item) VALUES (@item) 167 END -- @ix = 0 168 END -- @del_len = 1 169 170 RETURN 171 END 172 173 GO 174 175 /* 176 Overloaded version to make splitting comma seperated lists of ints easier. 177 Note the delimiter is hard coded to comma and that non-int values will be removed. 178 */ 179 CREATE FUNCTION [dbo].[FN_SPLIT_INT] ( 180 @Text nvarchar(max) -- Text to split 181 ) 182 RETURNS TABLE 183 AS 184 RETURN SELECT [item_int] -- TODO: Optional add distinct? 185 FROM [dbo].[FN_SPLIT](@Text, ‘,‘) -- Hard coded to comma delimited 186 WHERE [item_int] IS NOT NULL -- Remove invalid values 187 GO 188 189 190 GRANT REFERENCES, SELECT ON [dbo].[FN_SPLIT] TO [public] AS [dbo] 191 GRANT REFERENCES, SELECT ON [dbo].[FN_SPLIT_INT] TO [public] AS [dbo] 192 GO 193 194 -- Precompile, so performance numbers below are not skewed. 195 SELECT * INTO #Garbage1 FROM [dbo].[FN_SPLIT](‘1,2,3‘,‘,‘) 196 SELECT * INTO #Garbage2 FROM [dbo].[FN_SPLIT_INT](‘1,2,3‘) 197 198 DROP TABLE #Garbage1 199 DROP TABLE #Garbage2 200 GO 201 202 -------------------------------------------------------------------------------------------------------------------------------------------- 203 -------------------------------------------------------------------------------------------------------------------------------------------- 204 -------------------------------------------------------------------------------------------------------------------------------------------- 205 -- Unit test -- 206 -- Split has been problematic enough for me I thought these tests important. 207 208 SET NOCOUNT ON 209 210 DECLARE @TESTS TABLE ( 211 [cnt] int, 212 [txt] nvarchar(max), 213 [delim] nvarchar(1000), 214 [sum_len] int 215 ) 216 DECLARE @RESULTS TABLE ( 217 [id] int identity(1,1), 218 [msg] varchar(255) null, 219 [pass_fail] AS ( 220 CASE 221 WHEN (ISNULL([expected_count],0) = ISNULL([actual_count],0) AND ISNULL([expected_sum_len],0) = ISNULL([actual_sum_len],0)) THEN ‘PASS‘ 222 ELSE ‘FAIL‘ 223 END 224 ), 225 [runtime] int null, 226 [expected_count] int null, 227 [actual_count] int null, 228 [expected_sum_len] int null, 229 [actual_sum_len] int null, 230 [delim] nvarchar(1000), 231 [txt] nvarchar(max) 232 ) 233 234 DECLARE @BigText nvarchar(max) 235 DECLARE @BigTextItemCount int 236 DECLARE @BigTextSumHash int 237 238 -- Alternative large volume tests, set to 10 for quick, set to 100K for a real workout 239 --SELECT @BigTextItemCount = 10, @BigTextSumHash = 11 240 SELECT @BigTextItemCount = 10000, @BigTextSumHash = 38894 241 --SELECT @BigTextItemCount = 100000, @BigTextSumHash = 488895 242 243 -- Create the hash of big text. I know this code is somewhat ugly, but it creates the large text in 244 -- about 1 second, as opposed to an itterative concat that took 14 minutes... :-) 245 ;with cte as ( 246 select 9 as [i] 247 union all 248 select [i] - 1 FROM cte where [i] > 0 249 ), 250 crs as ( 251 SELECT ROW_NUMBER() OVER(ORDER BY c1.[i]) as [rn] 252 FROM cte c1 -- 10 253 CROSS JOIN cte c2 -- 100 254 CROSS JOIN cte c3 -- 1000 255 CROSS JOIN cte c4 -- 10000 256 CROSS JOIN cte c5 -- 100000 257 ) 258 SELECT @BigText = 259 ( 260 ( 261 SELECT ‘#‘ + CAST([rn] as nvarchar(32)) 262 FROM crs 263 WHERE [rn] <= @BigTextItemCount 264 FOR XML PATH(‘‘) , TYPE 265 ).value(‘.‘, ‘nvarchar(max)‘) 266 ) 267 268 -- Most of the tests go here -- 269 INSERT INTO @TESTS (cnt, sum_len, txt, delim) 270 -- Basic 1-char Delim Tests 271 SELECT 0, 0, ‘‘, ‘,‘ 272 UNION ALL SELECT 0, 0, null, ‘,‘ 273 UNION ALL SELECT 0, 0, ‘a‘, null 274 UNION ALL SELECT 0, 0, ‘a‘, ‘‘ 275 UNION ALL SELECT 3, 3, ‘1,2,3‘, ‘,‘ 276 UNION ALL SELECT 3, 3, ‘,1,2,3‘, ‘,‘ 277 UNION ALL SELECT 3, 3, ‘1,2,3,‘, ‘,‘ 278 UNION ALL SELECT 3, 3, ‘,1,2,3,‘, ‘,‘ 279 UNION ALL SELECT 3, 3, ‘ , 1 , 2 , 3 , ‘, ‘,‘ 280 UNION ALL SELECT 3, 3, ‘,,, , 1 , 2 , 3 , ,,,‘, ‘,‘ 281 UNION ALL SELECT 3, 3, ‘a, b, c‘, ‘,‘ 282 UNION ALL SELECT 3, 3, ‘a,b,c‘, ‘,‘ 283 UNION ALL SELECT 2, 6, ‘Cat=Pub‘, ‘=‘ 284 UNION ALL SELECT 1, 1, ‘a‘, ‘,‘ 285 UNION ALL SELECT 1, 1, ‘ a ‘, ‘,‘ 286 -- 1 char Int Tests 287 UNION ALL SELECT 10, 18, ‘a,1,2,-1,-2,b,1.0,-1.0, 3 , -4 ,‘, ‘,‘ 288 -- Basic multi-char delim tests 289 UNION ALL SELECT 0, 0, ‘‘, ‘<tag>‘ 290 UNION ALL SELECT 0, 0, null, ‘<tag>‘ 291 UNION ALL SELECT 0, 0, ‘a‘, null 292 UNION ALL SELECT 0, 0, ‘a‘, ‘‘ 293 UNION ALL SELECT 3, 3, ‘1<TaG>2<tag>3‘, ‘<tag>‘ -- Case Insensitivity test 1 294 UNION ALL SELECT 3, 3, ‘<tag>1<tag>2<tag>3‘, ‘<TaG>‘ -- Case Insensitivity test 2 295 UNION ALL SELECT 3, 3, ‘1<tag>2<tag>3<tag>‘, ‘<tag>‘ 296 UNION ALL SELECT 3, 3, ‘<tag>1<tag>2<tag>3<tag>‘, ‘<tag>‘ 297 UNION ALL SELECT 3, 3, ‘ <tag> 1 <tag> 2 <tag> 3 <tag> ‘, ‘<tag>‘ 298 UNION ALL SELECT 3, 3, ‘<tag><tag><tag> <tag> 1 <tag> 2 <tag> 3 <tag> <tag><tag><tag>‘, ‘<tag>‘ 299 UNION ALL SELECT 3, 3, ‘a<tag> b<tag> c‘, ‘<tag>‘ 300 UNION ALL SELECT 3, 3, ‘a<tag>b<tag>c‘, ‘<tag>‘ 301 UNION ALL SELECT 2, 6, ‘Cat<tag>Pub‘, ‘<tag>‘ 302 UNION ALL SELECT 1, 1, ‘a‘, ‘<tag>‘ 303 UNION ALL SELECT 1, 1, ‘ a ‘, ‘<tag>‘ 304 -- multi char delim Int Tests 305 UNION ALL SELECT 10, 18, ‘a<tag>1<tag>2<tag>-1<tag>-2<tag>b<tag>1.0<tag>-1.0<tag> 3 <tag> -4 <tag>‘, ‘<tag>‘ 306 -- Delims with escape char % in it 307 UNION ALL SELECT 0, 0, ‘‘, ‘<t%a%g>‘ 308 UNION ALL SELECT 0, 0, null, ‘<t%a%g>‘ 309 UNION ALL SELECT 0, 0, ‘a‘, null 310 UNION ALL SELECT 0, 0, ‘a‘, ‘‘ 311 UNION ALL SELECT 3, 3, ‘1<t%a%g>2<t%a%g>3‘, ‘<t%a%g>‘ 312 UNION ALL SELECT 3, 3, ‘<t%a%g>1<t%a%g>2<t%a%g>3‘, ‘<t%a%g>‘ 313 UNION ALL SELECT 3, 3, ‘1<t%a%g>2<t%a%g>3<t%a%g>‘, ‘<t%a%g>‘ 314 UNION ALL SELECT 3, 3, ‘<t%a%g>1<t%a%g>2<t%a%g>3<t%a%g>‘, ‘<t%a%g>‘ 315 UNION ALL SELECT 3, 3, ‘ <t%a%g> 1 <t%a%g> 2 <t%a%g> 3 <t%a%g> ‘, ‘<t%a%g>‘ 316 UNION ALL SELECT 3, 3, ‘<t%a%g><t%a%g><t%a%g> <t%a%g> 1 <t%a%g> 2 <t%a%g> 3 <t%a%g> <t%a%g><t%a%g><t%a%g>‘, ‘<t%a%g>‘ 317 UNION ALL SELECT 3, 3, ‘a<t%a%g> b<t%a%g> c‘, ‘<t%a%g>‘ 318 UNION ALL SELECT 3, 3, ‘a<t%a%g>b<t%a%g>c‘, ‘<t%a%g>‘ 319 UNION ALL SELECT 2, 6, ‘Cat<t%a%g>Pub‘, ‘<t%a%g>‘ 320 UNION ALL SELECT 1, 1, ‘a‘, ‘<t%a%g>‘ 321 UNION ALL SELECT 1, 1, ‘ a ‘, ‘<t%a%g>‘ 322 UNION ALL SELECT 10, 18, ‘a<t%a%g>1<t%a%g>2<t%a%g>-1<t%a%g>-2<t%a%g>b<t%a%g>1.0<t%a%g>-1.0<t%a%g> 3 <t%a%g> -4 <t%a%g>‘, ‘<t%a%g>‘ 323 -- Delims with escape char _ in it 324 UNION ALL SELECT 0, 0, ‘‘, ‘<t_ag>‘ 325 UNION ALL SELECT 0, 0, null, ‘<t_ag>‘ 326 UNION ALL SELECT 0, 0, ‘a‘, null 327 UNION ALL SELECT 0, 0, ‘a‘, ‘‘ 328 UNION ALL SELECT 3, 3, ‘1<t_ag>2<t_ag>3‘, ‘<t_ag>‘ 329 UNION ALL SELECT 3, 3, ‘<t_ag>1<t_ag>2<t_ag>3‘, ‘<t_ag>‘ 330 UNION ALL SELECT 3, 3, ‘1<t_ag>2<t_ag>3<t_ag>‘, ‘<t_ag>‘ 331 UNION ALL SELECT 3, 3, ‘<t_ag>1<t_ag>2<t_ag>3<t_ag>‘, ‘<t_ag>‘ 332 UNION ALL SELECT 3, 3, ‘ <t_ag> 1 <t_ag> 2 <t_ag> 3 <t_ag> ‘, ‘<t_ag>‘ 333 UNION ALL SELECT 3, 3, ‘<t_ag><t_ag><t_ag> <t_ag> 1 <t_ag> 2 <t_ag> 3 <t_ag> <t_ag><t_ag><t_ag>‘, ‘<t_ag>‘ 334 UNION ALL SELECT 3, 3, ‘a<t_ag> b<t_ag> c‘, ‘<t_ag>‘ 335 UNION ALL SELECT 3, 3, ‘a<t_ag>b<t_ag>c‘, ‘<t_ag>‘ 336 UNION ALL SELECT 2, 6, ‘Cat<t_ag>Pub‘, ‘<t_ag>‘ 337 UNION ALL SELECT 1, 1, ‘a‘, ‘<t_ag>‘ 338 UNION ALL SELECT 1, 1, ‘ a ‘, ‘<t_ag>‘ 339 UNION ALL SELECT 10, 18, ‘a<t_ag>1<t_ag>2<t_ag>-1<t_ag>-2<t_ag>b<t_ag>1.0<t_ag>-1.0<t_ag> 3 <t_ag> -4 <t_ag>‘, ‘<t_ag>‘ 340 -- Semi Evil tests 341 UNION ALL SELECT 2, 2, ‘a~`!@#$%^&*()_+|-=\{}:;"‘‘<>,.?/ b‘, ‘~`!@#$%^&*()_+|-=\{}:;"‘‘<>,.?/ ‘ -- no [] 342 UNION ALL SELECT 2, 2, ‘a~`!@#$%^&*()_+|-=\{}[]:;"‘‘<>,.?/ b‘, ‘~`!@#$%^&*()_+|-=\{}[]:;"‘‘<>,.?/ ‘ -- with [] 343 UNION ALL SELECT 2, 2, ‘a‘ + CHAR(10) + CHAR(13) + ‘b‘, CHAR(10) + CHAR(13) -- White space chars 344 -- Big Text Tests 345 UNION ALL SELECT @BigTextItemCount,@BigTextSumHash,@BigText, ‘#‘ 346 UNION ALL SELECT @BigTextItemCount,@BigTextSumHash,REPLACE(@BigText,‘#‘, ‘<tag>‘), ‘<tag>‘ 347 348 -- Loop through each of the tests, logging results 349 DECLARE @txt nvarchar(max) -- Input text 350 DECLARE @delim nvarchar(1000) -- Input delimiter 351 DECLARE @cnt int -- Expected count 352 DECLARE @sum_len int -- Expected sum(len(item)) 353 DECLARE @t_cnt int -- Actual count 354 DECLARE @t_sum_len int -- Actual sum(len(item)) 355 DECLARE @start datetime -- Test Start time (for performance tracking) 356 357 DECLARE cur CURSOR FAST_FORWARD FOR 358 SELECT [cnt],[txt],[delim],[sum_len] FROM @TESTS 359 OPEN cur 360 FETCH cur INTO @cnt, @txt, @delim,@sum_len 361 WHILE @@FETCH_STATUS = 0 362 BEGIN 363 364 SELECT @start = GetDate(); 365 366 -- Execute test 367 SELECT @t_cnt = count(*), @t_sum_len = SUM(LEN(item)) 368 FROM [dbo].[FN_SPLIT](@txt, @delim) 369 370 -- Log results 371 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 372 SELECT ‘TEST‘, DATEDIFF(ms, @start, GetDate()), @cnt, @t_cnt, @sum_len, ISNULL(@t_sum_len,0), @delim, @txt 373 374 FETCH cur INTO @cnt, @txt, @delim,@sum_len 375 END 376 CLOSE cur 377 DEALLOCATE cur 378 379 ---------------------------------------------------------------------------------------------------------------------------------- 380 -- Extra tests that required additional coding 381 DECLARE @int_test nvarchar(max) 382 SELECT @int_test = N‘a,1,2,-1,-2,b,1.0,-1.0, 3 , -4 ,‘ 383 384 -- Basic int test, ensure int‘s are properly returned 385 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 386 SELECT ‘Tested Ints 1‘, null, 6, count(*), null, null, ‘,‘, @int_test 387 FROM [dbo].[FN_SPLIT](@int_test, ‘,‘) 388 WHERE [item_int] is not null 389 390 -- Ensure text value associated with int values maps 1:1 391 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 392 SELECT ‘Tested Ints 2‘, null, 6, count(*), null, null, ‘,‘, @int_test 393 FROM [dbo].[FN_SPLIT](@int_test, ‘,‘) 394 WHERE CAST([item_int] as nvarchar(max)) = [item] 395 and item_int is not null 396 397 398 -- Split int tests 399 SELECT @int_test = ‘1,-2,3‘ 400 SELECT @start = GetDate(); 401 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 402 SELECT ‘Split Int: ‘ + @int_test, DATEDIFF(ms, @start, GetDate()), 3, count(*), 2, SUM(item_int), ‘#‘, @int_test 403 FROM [dbo].[FN_SPLIT_INT](@int_test) 404 405 SELECT @int_test = ‘1,a,-2,b,3,c‘ 406 SELECT @start = GetDate(); 407 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 408 SELECT ‘Split Int: ‘ + @int_test, DATEDIFF(ms, @start, GetDate()), 3, count(*), 2, SUM(item_int), ‘#‘, @int_test 409 FROM [dbo].[FN_SPLIT_INT](@int_test) 410 411 SELECT @int_test = ‘1, -2, 3‘ -- Spaces between commas 412 SELECT @start = GetDate(); 413 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 414 SELECT ‘Split Int: ‘ + @int_test, DATEDIFF(ms, @start, GetDate()), 3, count(*), 2, SUM(item_int), ‘#‘, @int_test 415 FROM [dbo].[FN_SPLIT_INT](@int_test) 416 417 SELECT @int_test = ‘ 1, -2, 3 ‘ -- Leading/trailing 418 SELECT @start = GetDate(); 419 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 420 SELECT ‘Split Int: ‘ + @int_test, DATEDIFF(ms, @start, GetDate()), 3, count(*), 2, SUM(item_int), ‘#‘, @int_test 421 FROM [dbo].[FN_SPLIT_INT](@int_test) 422 423 SELECT @int_test = ‘999999999999999,1,-2,-3,-99999999999999999‘ -- Basic boundry testing 424 SELECT @start = GetDate(); 425 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 426 SELECT ‘Split Int: ‘ + @int_test, DATEDIFF(ms, @start, GetDate()), 3, count(*), -4, SUM(item_int), ‘#‘, @int_test 427 FROM [dbo].[FN_SPLIT_INT](@int_test) 428 429 SELECT @int_test = ‘ 1.0, -2.0, 3 ‘ -- Should only return ints 430 SELECT @start = GetDate(); 431 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 432 SELECT ‘Split Int: ‘ + @int_test, DATEDIFF(ms, @start, GetDate()), 1, count(*), 3, SUM(item_int), ‘#‘, @int_test 433 FROM [dbo].[FN_SPLIT_INT](@int_test) 434 435 ---------------------------------------------------------------------------------------------------------------------------------- 436 -- Runtime / Performance testing 437 438 IF OBJECT_ID(‘tempdb..#t1‘) IS NOT NULL DROP TABLE #t1 439 IF OBJECT_ID(‘tempdb..#t2‘) IS NOT NULL DROP TABLE #t2 440 IF OBJECT_ID(‘tempdb..#t3‘) IS NOT NULL DROP TABLE #t3 441 442 SELECT @start = GetDate(); 443 SELECT [item] INTO #t1 FROM [dbo].[FN_SPLIT](@BigText, ‘#‘) 444 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 445 SELECT ‘Speed 1: Item only‘, DATEDIFF(ms, @start, GetDate()), null, null, null, null, ‘#‘, @BigText 446 447 448 SELECT @start = GetDate(); 449 SELECT [item_int] INTO #t3 FROM [dbo].[FN_SPLIT](@BigText, ‘#‘) 450 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 451 SELECT ‘Speed 2: Item Int Only‘, DATEDIFF(ms, @start, GetDate()), null, null, null, null, ‘#‘, @BigText 452 453 SELECT @start = GetDate(); 454 SELECT [item] INTO #t2 FROM [dbo].[FN_SPLIT](@BigText, ‘#‘) WHERE [item_int] IS NOT NULL 455 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 456 SELECT ‘Speed 3: Item With Int Filter‘, DATEDIFF(ms, @start, GetDate()), null, null, null, null, ‘#‘, @BigText 457 458 IF OBJECT_ID(‘tempdb..#t1‘) IS NOT NULL DROP TABLE #t1 459 IF OBJECT_ID(‘tempdb..#t2‘) IS NOT NULL DROP TABLE #t2 460 IF OBJECT_ID(‘tempdb..#t3‘) IS NOT NULL DROP TABLE #t3 461 462 ---------------------------------------------------------------------------------------------------------------------------------- 463 /* 464 -- Ensure test failures work 465 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) 466 SELECT ‘INTENTIONAL FAILURE‘, null, 1, 2, 3, 4, ‘‘, ‘‘ 467 */ 468 469 -- Display results 470 SELECT * 471 FROM @RESULTS 472 ORDER BY CASE [pass_fail] WHEN ‘FAIL‘ THEN 0 ELSE 1 END ASC, [id] ASC 473 474 -- And Total runtime 475 SELECT SUM(ISNULL(runtime,0)) as [total_runtime] FROM @RESULTS 476 477 -- Raise errors as needed. 478 IF (SELECT count(*) FROM @RESULTS WHERE [pass_fail] = ‘FAIL‘) > 0 479 RAISERROR(‘Unexpected results. Review results table for details.‘,18,1) 480 GO
1 SET ANSI_NULLS ON; 2 SET ANSI_PADDING ON; 3 SET ANSI_WARNINGS ON; 4 SET CONCAT_NULL_YIELDS_NULL ON; 5 SET NUMERIC_ROUNDABORT OFF; 6 SET QUOTED_IDENTIFIER ON; 7 GO 8 9 IF OBJECT_ID(‘[dbo].[PR_FIND]‘) IS NOT NULL 10 DROP PROCEDURE [dbo].[PR_FIND] 11 GO 12 13 CREATE PROCEDURE [dbo].[PR_FIND] 14 /* ------------------------------------------------------------------------------------------------ 15 Purpose: Examples (finding self, since we know it exists) 16 17 EXEC [PR_FIND] ‘@DisplayLevel‘ 18 EXEC [PR_FIND] ‘PR_FIND‘, 0 19 EXEC [PR_FIND] ‘PR_FIND‘, 1, 50 20 21 22 Revision history: 23 --------------------------------------------------------------------------------------------------- 24 Date User Change Description 25 --------------------------------------------------------------------------------------------------- 26 10/11/2013 Brad Joss (bradjoss@hotmail.com) Initial Public Draft 27 01/20/2014 Brad Joss Fixed line number bug where line numbers were incorrect. 28 01/23/2014 Brad Joss When fixing the above bug, I saw some items that might 29 be nice to have, so I added a few features: 30 - Will now match on name, not just body text. Name can be fully qualified 31 since functionally it will do an OBJECT_ID() match. 32 - Display more details in run summary including total matches 33 and a better breakdown of where time was spent while searching. 34 - Display match_count and name_match in summary results. 35 ---------------------------------------------------------------------------------------------------*/ 36 ( 37 @TextToFind nvarchar(max), -- Pattern to search on 38 @DisplayLevel tinyint = 1, -- 0 = Object Summary Only, 1 = Windowed Results, 2 = Full text 39 @Lead int = 40, -- Amount of text to show the left and right of the result in Windowed mode 40 @SearchJobsToo bit = 1, -- When true, search SQL Agent Job Steps 41 @IncludeObjects nvarchar(max) = null, -- Comma separated list of objects scope search to 42 @ExcludeObjects nvarchar(max) = null -- Comma separated list of objects exempt from search 43 ) 44 AS 45 BEGIN 46 SET NOCOUNT ON 47 48 DECLARE @id int -- Object ID 49 , @name nvarchar(max) -- Full Object Name 50 , @Text nvarchar(max) -- Object Text 51 , @DisplayText nvarchar(max) -- Text to Output 52 , @index int -- index of start of display window (@match_index - @Lead) 53 , @match_index int -- Actual index where we found the match 54 , @matchCount int -- Matches found for the text 55 , @spanLen int -- Length of display window 56 , @lenText int -- Curent LEN(@Text) 57 , @lenTextToFind int -- LEN(@TextToFind) 58 , @StartTime DateTime -- Runtime Start 59 , @OriginalTextToFind nvarchar(max) -- Backup of what is being searched on before we adjust it 60 , @create_date datetime -- Object create date 61 , @modify_date datetime -- Object mod date 62 , @type_desc nvarchar(60) -- Object type 63 , @total_lines int -- Runing total of found new lines 64 , @current_line_number int -- Line number current match is found on (starting from CREATE statement) 65 , @total_start_time datetime -- Start time of the SP call, for kicks 66 , @skip_lines int -- When we skip a body of text, keep track of line count for skipped text 67 , @new_line nvarchar(max) -- Character(s) to seek when counting new lines 68 , @scan_time int -- Time it took to scan for matches 69 , @passed_object_id int -- If they passed in a full object name, let‘s know about it here... 70 71 72 -- Just to make sure we are keeping the code well optimzed, display runtimes 73 SELECT @total_start_time = GetDate() 74 75 -- Get this before we do any transformations on the text 76 SELECT @passed_object_id = OBJECT_ID(@TextToFind) 77 78 -- Backup what is being searched on so when we display it it doesn‘t look funny 79 SELECT @OriginalTextToFind = @TextToFind 80 81 -- Wrap the text in wild card wrappers and remove %%, setting to LOWER() so all matches will be case insenstive later on 82 SELECT @TextToFind = REPLACE(‘%‘ + LOWER(@TextToFind) + ‘%‘, ‘%%‘,‘%‘) 83 84 85 -- If they passed something that maps 1:1 to an object, escape out the bracets so we match on object name, 86 -- not character array patterns when searching on text. The actual name match will be based on object_id 87 IF @passed_object_id IS NOT NULL 88 SELECT @TextToFind = REPLACE(@TextToFind,‘[‘,‘[[]‘) 89 90 -- Even if we actually use 10/13, the way we are counting should still work. 91 -- If you move to more than 1 newline character, then the character count will have 92 -- to be factored into the delta counts (e.g. (LEN() - LEN(REPLACE()))/LEN(@new_line)) 93 SELECT @new_line = CHAR(10) 94 95 -- Parmeter checks 96 IF ISNULL(@DisplayLevel,-1) NOT IN (0,1,2) 97 SET @DisplayLevel = 1 -- Default to windowed mode 98 IF @Lead IS NULL OR @Lead < 0 99 SET @Lead = 40 -- Default lead lenght 100 IF @SearchJobsToo IS NULL 101 SET @SearchJobsToo = 1 102 IF RTRIM(@IncludeObjects) = ‘‘ 103 SET @IncludeObjects = NULL 104 IF RTRIM(@ExcludeObjects) = ‘‘ 105 SET @ExcludeObjects = NULL 106 107 108 -- Table to store the matched objects 109 DECLARE @oids TABLE ( 110 [id] int, -- [object_id] 111 [name] nvarchar(512), 112 [schema_name] nvarchar(512), 113 [full_name] as ( 114 ISNULL(‘[‘ + [schema_name] + ‘].‘, ‘‘) + ‘[‘ + [name] + ‘]‘ 115 ), 116 [create_date] datetime, 117 [modify_date] datetime, 118 [type_desc] nvarchar(60), 119 [txt] nvarchar(max), 120 [name_match] bit default(0), 121 [match_count] int null 122 ) 123 124 -- Table to store what objects to return 125 DECLARE @scope_objects TABLE ( 126 [object_id] int, 127 [include] bit not null default(0) 128 ) 129 130 -- If they have indicated they want to filter by object name, create a list of 131 -- valid objects to be searched. 132 IF @IncludeObjects IS NOT NULL OR @ExcludeObjects IS NOT NULL 133 BEGIN 134 -- Explicitly omit these items 135 ;with cte as ( 136 SELECT o.id 137 FROM sysobjects o 138 INNER JOIN [dbo].[FN_SPLIT](@ExcludeObjects,‘,‘) f 139 on (UPPER(o.name) LIKE LTRIM(RTRIM(UPPER([item])))) -- Case insensitive non-wildcard match, so we can actually match on a specific object name if need be 140 AND RTRIM([item]) <> ‘‘ 141 ) 142 INSERT INTO @scope_objects ([object_id],[include]) 143 SELECT DISTINCT [id], 0 144 FROM cte 145 WHERE [id] IS NOT NULL 146 147 IF @@ROWCOUNT = 0 148 SELECT @ExcludeObjects = NULL -- Later, we check to see if we should filter based on @ExcludeObjects and @IncludeObjects nullness 149 150 -- Scope search to only include these items 151 ;with cte as ( 152 SELECT o.id 153 FROM sysobjects o 154 INNER JOIN [dbo].[FN_SPLIT](@IncludeObjects,‘,‘) f 155 on (UPPER(o.name) LIKE LTRIM(RTRIM(UPPER([item])))) -- Case insensitive non-wildcard match, so we can actually match on a specific object name if need be 156 AND RTRIM([item]) <> ‘‘ 157 ) 158 INSERT INTO @scope_objects ([object_id],[include]) 159 SELECT DISTINCT [id], 1 160 FROM cte 161 WHERE [id] IS NOT NULL 162 163 IF @@ROWCOUNT = 0 164 SELECT @IncludeObjects = NULL -- Later, we check to see if we should filter based on @ExcludeObjects and @IncludeObjects nullness 165 ELSE 166 BEGIN 167 -- If they have indicated that the want to include and exclude at the same time 168 -- check for object overlap, and default to exclude the item so we can allow a 169 -- pattern like: 170 -- Show me all where "aaa%", except "aaa_111" 171 DELETE FROM @scope_objects 172 WHERE [include] = 1 173 AND [object_id] IN ( 174 SELECT [object_id] 175 FROM @scope_objects 176 WHERE [include] = 0 177 ) 178 179 -- If items were deleted, recheck to see if there are any includes left 180 IF @@ROWCOUNT > 0 181 IF NOT EXISTS (SELECT * FROM @scope_objects WHERE [include] = 1) 182 SELECT @IncludeObjects = NULL, @ExcludeObjects = NULL -- Later, we check to see if we should filter based on @ExcludeObjects and @IncludeObjects nullness 183 END 184 END -- @IncludeObjects IS NOT NULL OR @ExcludeObjects IS NOT NULL 185 186 -- Find matches 187 INSERT INTO @oids (id, name, [schema_name], [type_desc], txt, [create_date], [modify_date], [name_match]) 188 select distinct c.id, c.name, [schema_name], [type_desc], c.txt, [create_date], [modify_date], 189 CASE 190 -- Substring of a name is what was passed 191 WHEN LOWER(name) LIKE @TextToFind THEN 1 192 WHEN 193 ( 194 -- Fully qualified name was passed 195 @passed_object_id IS NOT NULL 196 AND 197 c.id = @passed_object_id 198 ) THEN 1 199 ELSE 0 END 200 from [dbo].[VW_OBJ_TXT] c 201 -- That case insensitive match our search text 202 where 203 ( 204 LOWER(c.[txt]) like @TextToFind -- Body match 205 OR 206 LOWER(name) LIKE @TextToFind -- Name match 207 OR 208 ( 209 -- In case they pass in "[dbo].[MyObject]" versus "dbo.MyObject" versus "MyObject" 210 -- Try to give them clues as to what they may be missing out on 211 @passed_object_id IS NOT NULL 212 AND 213 c.id = @passed_object_id 214 ) 215 ) 216 -- And are either in our include list, or no list was passed 217 and 218 ( 219 @IncludeObjects is null 220 OR 221 c.id IN (select [object_id] from @scope_objects where [include] = 1) 222 ) 223 -- And are not in our exclude list, or no list was passed 224 and 225 ( 226 @ExcludeObjects is null 227 OR 228 c.id NOT IN (select [object_id] from @scope_objects where [include] = 0) 229 ) 230 231 -- If they have indicated to search job text, do so here. 232 IF @SearchJobsToo = 1 233 BEGIN 234 INSERT INTO @oids (id, name, [schema_name], [type_desc], txt, [create_date], [modify_date], [name_match]) 235 SELECT DISTINCT 236 (-1 * ROW_NUMBER()OVER(ORDER BY j.name, js.step_name)), -- Since job_id‘s are not int, job_step_id might be confusing, get arbitrary negative number 237 ISNULL(‘JOB: ‘ + j.name, ‘Unknown Job‘) + ISNULL(‘, STEP: ‘ + js.step_name, ‘Unknown Step‘), 238 ‘job‘, 239 ‘SQL Agent Job Step‘, 240 js.command, -- Job Step Text 241 j.date_created, 242 j.date_modified, 243 CASE 244 WHEN LOWER(j.name) LIKE @TextToFind THEN 1 245 WHEN LOWER(js.step_name) LIKE @TextToFind THEN 1 246 ELSE 0 247 END 248 FROM msdb.dbo.sysjobs j 249 JOIN msdb.dbo.sysjobsteps js 250 ON js.job_id = j.job_id 251 WHERE 252 ( 253 LOWER(js.command) like @TextToFind -- Case insensitive 254 OR 255 LOWER(j.name) LIKE @TextToFind 256 OR 257 LOWER(js.step_name) LIKE @TextToFind 258 ) 259 END 260 261 SELECT @scan_time = DATEDIFF(ms, @total_start_time, GetDate()) 262 263 IF @DisplayLevel > 0 264 BEGIN 265 -- Horizontal rules to break up the results 266 DECLARE @C_OBJECT_HR nvarchar(max) = ‘/******************************************************************************/‘ 267 DECLARE @C_MATCH_HR nvarchar(max) = ‘--------------------------------------------------------------------------------‘ 268 269 -- Cache this value once before we enter into loop 270 SELECT @lenTextToFind = LEN(@TextToFind) - 2 -- -2 = Trimming the % 271 IF @lenTextToFind < 0 272 SELECT @lenTextToFind = 0 273 274 PRINT @C_OBJECT_HR 275 276 -- Loop though the results, getting the multiple matches within the body of the text 277 DECLARE DispCur CURSOR FAST_FORWARD FOR 278 SELECT 279 o.id, 280 [full_name], 281 o.txt, 282 create_date, 283 modify_date, 284 type_desc 285 from @oids o 286 ORDER BY LOWER([full_name]) ASC 287 288 OPEN DispCur 289 FETCH DispCur INTO @id, @name, @Text, @create_date, @modify_date, @type_desc 290 291 WHILE @@FETCH_STATUS = 0 292 BEGIN 293 -- Object match template, add details here to display information about the match per object 294 PRINT ‘ID: ‘ + CAST(@id as varchar(64)) 295 PRINT ‘NAME: ‘ + @name 296 PRINT ‘TYPE: ‘ + @type_desc 297 PRINT ‘CREATED: ‘ + CAST(@create_date as nvarchar(max)) + ‘, MODIFIED: ‘ + CAST(@modify_date as nvarchar(max)) 298 PRINT ‘SEARCH: "‘ + @OriginalTextToFind + ‘"‘ 299 PRINT @C_MATCH_HR 300 301 IF @DisplayLevel = 1 -- Windowed display mode 302 BEGIN 303 SELECT @StartTime = GetDate() -- For the search time of this one object (not the whole routine), for kicks 304 SELECT @index = PATINDEX(@TextToFind, LOWER(@Text)) -- Search for our matching pattern 305 SELECT @match_index = @index 306 SELECT @matchCount = 0 307 SELECT @DisplayText = ‘‘ 308 SELECT @total_lines = 0 309 310 -- Find all occurences of the pattern -- 311 WHILE @index > 0 312 BEGIN 313 -- Get the count of new line characters, then adding on matches from previous blocks of text 314 SELECT @current_line_number = (LEN(SUBSTRING(@Text, 1, @index)) - LEN(REPLACE(SUBSTRING(@Text, 1, @index), @new_line, ‘‘))) 315 316 -- Buffer the common search values in variables 317 SELECT @matchCount = @matchCount + 1 318 SELECT @lenText = LEN(@Text) 319 -- Set the start @index in bounds 320 SELECT @index = CASE 321 WHEN @index > @Lead THEN @index - @Lead 322 ELSE 0 323 END 324 -- Size of the display window 325 SELECT @spanLen = LEN(@TextToFind) + (2*@Lead) 326 327 -- If the search window is longer than the search text, narrow it 328 IF @spanLen + @index > @lenText 329 SELECT @spanLen = @lenText - @index 330 331 -- Display code snippet -- 332 SELECT @DisplayText = @DisplayText + ‘ 333 Match ‘ + CAST(@matchCount as varchar(32)) + ‘ on line ‘ + CAST((@current_line_number + @total_lines) as varchar(32)) + ‘ within ‘ + @name + ‘ 334 ‘ + @C_MATCH_HR + ‘ 335 ...‘ + SUBSTRING(@Text, @index, @spanLen) + ‘... 336 ‘ 337 338 -- If the search window covered to the end of the text, end searching 339 IF (@match_index + @Lead) >= @lenText 340 SELECT @index = 0 341 ELSE 342 BEGIN 343 -- Keep track of how many lines will be skipped by advancing the seek start by the window length 344 SELECT @skip_lines = LEN(SUBSTRING(@Text, @match_index, @Lead)) - LEN(REPLACE(SUBSTRING(@Text, @match_index, @Lead), @new_line, ‘‘)) 345 346 -- Else rescope the text to be searched to what remains and re-search 347 SELECT @Text = SUBSTRING 348 ( 349 @Text, 350 @match_index + @Lead, 351 @lenText - (@match_index + @Lead) 352 ) 353 SELECT @index = PATINDEX(@TextToFind, LOWER(@Text)) -- Find the next match 354 SELECT @match_index = @index 355 SELECT @total_lines = @total_lines + @current_line_number + @skip_lines -- Keep running total of line numbers 356 END 357 END -- While (finding all matches in object) 358 359 IF @matchCount = 0 360 SELECT @DisplayText = @DisplayText + ‘No body matches found, name match only.‘ 361 362 -- Footer template, displayed at the end of each object that matches 363 SELECT @DisplayText = @DisplayText + ‘ 364 ‘ + @C_MATCH_HR + ‘ 365 "‘ + @OriginalTextToFind + ‘" Found ‘ 366 + CAST(@matchCount as varchar(32)) + ‘ Time‘ + (CASE WHEN @matchCount = 1 THEN ‘‘ ELSE ‘s‘ END) + ‘ within ‘ + @name + ‘ in ‘ 367 + CAST(DATEDIFF(ms, @StartTime, GetDate()) as varchar(32)) + ‘ms 368 ‘ 369 370 EXEC PR_PRINT @DisplayText 371 372 UPDATE @oids SET [match_count] = @matchCount WHERE [id] = @id 373 374 END -- @DisplayLevel = 1 375 ELSE -- Mode 2 376 BEGIN 377 -- ELSE Display full code -- 378 EXEC PR_PRINT @Text 379 END 380 381 PRINT @C_OBJECT_HR 382 383 FETCH DispCur INTO @id, @name, @Text, @create_date, @modify_date, @type_desc 384 END 385 CLOSE DispCur 386 DEALLOCATE DispCur 387 END -- @DisplayLevel > 0 388 389 390 -- Display summary at the bottom so we have match counts -- 391 -- I would prefer this to be at the top, but I like the match count... 392 SELECT 393 [id], 394 CONVERT(varchar(19), [modify_date], 120) as [modify_date], 395 [type_desc], 396 [full_name], 397 [name_match], 398 [match_count] 399 from @oids t 400 ORDER BY LOWER(t.name) ASC 401 402 DECLARE @Message nvarchar(max) 403 DECLARE @TotalRuntime int 404 DECLARE @TotalMatches int 405 406 SELECT @TotalMatches = 407 (SELECT SUM(match_count) FROM @oids) 408 + 409 (SELECT COUNT(*) FROM @oids where [name_match] = 1); 410 411 SELECT @TotalRuntime = DATEDIFF(ms, @total_start_time, GetDate()) 412 SELECT @Message = ‘Search completed. ‘ 413 + ‘Found ‘ + CAST(@TotalMatches as nvarchar(max)) +‘ match‘ + CASE WHEN @TotalMatches = 1 THEN ‘‘ ELSE ‘es‘ END + ‘. ‘ 414 + CAST(@scan_time as nvarchar(max)) +‘ms Scan Time. ‘ 415 + CAST((@TotalRuntime - @scan_time) as nvarchar(max)) + ‘ms Format Time. ‘ 416 + ‘Total Runtime: ‘ + CAST((@TotalRuntime + 500)/1000 as nvarchar(max)) + ‘ seconds.‘ -- + 500 so we round up at 1/2 second 417 PRINT ‘‘ 418 PRINT @Message 419 END 420 GO 421 422 GRANT EXECUTE ON [dbo].[PR_FIND] TO [public] AS [dbo] 423 GO
1 IF OBJECT_ID(‘dbo.PR_PRINT‘) IS NOT NULL 2 DROP PROCEDURE dbo.PR_PRINT 3 GO 4 5 CREATE PROCEDURE [dbo].[PR_PRINT] 6 ( 7 @txt NVARCHAR(MAX) -- Text to print out 8 ) 9 AS 10 11 /* 12 This was originally posted on SQLServerCentral.com at 13 http://www.sqlservercentral.com/scripts/Print/63240/ 14 15 Modifed by Brad Joss 10/11/13 to break on space as well 16 17 This procedure is designed to overcome the limitation 18 in the SQL print command that causes it to truncate strings 19 longer than 8000 characters (4000 for nvarchar) in SQL Server 20 management studio. 21 22 It will print the text passed to it in substrings smaller than 4000 23 characters. If there are carriage returns (CRs) or new lines (NLs in the text), 24 it will break up the substrings at the carriage returns and the 25 printed version will exactly reflect the string passed. 26 27 If there are insufficient line breaks in the text, it will 28 print it out in blocks of 4000 characters with an extra carriage 29 return at that point. 30 31 If it is passed a null value, it will do virtually nothing. 32 33 NOTE: This is substantially slower than a simple print, so should only be used 34 when actually needed. 35 */ 36 37 SET NOCOUNT ON 38 39 DECLARE 40 @cur_end BIGINT, -- track the length of the next substring 41 @offset tinyint, -- tracks the amount of offSET needed 42 @pg_sz int, -- size of printing window 43 @char_idx bigint, -- index of the next newline character in the window 44 @temp_char_idx bigint, -- to avoid having to substring/reverse the window twice, save the reverse index of a found space here 45 @start_idx int, -- try to break on a known character combination 46 @txt_len bigint -- current lenght of the text, basically a cache since we potentially will need it twice in the loop 47 48 SET @pg_sz = 4000 -- Set window size to 4000 characters 49 50 -- If size is in bounds (ie, small text), just print it and exit 51 IF LEN(@txt) <= @pg_sz 52 BEGIN 53 PRINT @txt 54 RETURN 55 END 56 57 SET @txt = replace( replace(@txt, char(13) + char(10), char(10)) , char(13), char(10)) -- Standardize what a new line looks like to char(10) 58 SELECT @txt_len = LEN(@txt) 59 60 WHILE @txt_len > 1 61 BEGIN 62 63 -- Try to break on a new line 64 SET @char_idx = CHARINDEX(CHAR(10), @txt) 65 66 -- If we can‘t find a new line, try to break on a space where the space is near 67 -- the end of the current page of text 68 IF NOT (@char_idx between 1 AND @pg_sz) 69 BEGIN 70 -- Get the size of the page of text 71 SELECT @char_idx = CASE WHEN (@txt_len < @pg_sz) THEN @txt_len ELSE @pg_sz END 72 73 -- Look for the last space character in the page of text 74 SET @temp_char_idx = CHARINDEX(‘ ‘, REVERSE(SUBSTRING(@txt, 1, @char_idx))) 75 76 -- If found, set the position of the found character on the non-reversed string 77 IF @temp_char_idx > 0 78 SET @char_idx = (@char_idx - @temp_char_idx) + 1 -- +1 here since we -1 later on 79 ELSE -- Indicate character is still not found 80 SET @char_idx = 0 81 END 82 83 -- Try to break on a known char (newline or space) -- 84 IF @char_idx between 1 AND @pg_sz 85 BEGIN 86 -- Since we know the character we are breaking on is white space (new line or space) 87 -- don‘t print it (hence the -1) 88 SET @cur_end = @char_idx - 1 89 SET @offset = 2 90 END 91 ELSE 92 BEGIN 93 -- Else, just break at the window size. 94 SET @cur_end = @pg_sz 95 SET @offset = 1 96 END 97 98 -- Print the section 99 PRINT SUBSTRING(@txt, 1, @cur_end) 100 101 -- Remote the printed text from what remains to be printed. 102 SET @txt = SUBSTRING(@txt, @cur_end+@offset, 1073741822) 103 SELECT @txt_len = LEN(@txt) 104 105 END /*End While loop*/ 106 107 -- Print any leftovers 108 PRINT @txt 109 110 GO 111 112 GRANT EXECUTE ON [dbo].[PR_PRINT] TO [public] 113 GO 114 115 PRINT ‘Basic Test:‘ 116 EXEC dbo.PR_PRINT ‘aaa bbb ccc d‘ 117 118 119 PRINT ‘‘ 120 PRINT ‘More Complicated Test:‘ 121 122 DECLARE @BigText nvarchar(max) 123 DECLARE @WindowSize int 124 SET @WindowSize = 4000 125 SELECT @BigText = CAST(REPLICATE(‘a‘,@WindowSize-1) as nvarchar(max)) 126 + CAST(‘ ‘ as nvarchar(max)) 127 + CAST(REPLICATE(‘b‘,@WindowSize-1) as nvarchar(max)) 128 + CAST(CHAR(10) as nvarchar(max)) 129 + CAST(REPLICATE(‘c‘,@WindowSize-1) as nvarchar(max)) 130 + CAST(‘xxx‘ as nvarchar(max)) 131 + CAST(REPLICATE(‘d‘,@WindowSize-1)as nvarchar(max)) 132 133 134 EXEC dbo.PR_PRINT @BigText 135 GO
IF OBJECT_ID(‘VW_OBJ_TXT‘) IS NOT NULL DROP VIEW [dbo].[VW_OBJ_TXT] GO CREATE VIEW [dbo].[VW_OBJ_TXT] /******************************************************************************* * Name : dbo.VW_OBJ_TXT * Author : bradjoss@hotmail.com * Purpose : Helper view to make searching thorough code easier ****************************************************************************** * Change Date Change By Change DSC * ----------- ------------- -------------------------------------- * 10/05/2012 Brad Joss Initial Draft * 01/24/2014 Brad Joss Return records with null text bodies, like tables. Nixed cast on get from syscomments, it was unnecessary. *******************************************************************************/ AS with cte as ( select o.[object_id] as [id], o.[schema_id], SCHEMA_NAME(o.[schema_id]) as [schema_name], o.name, o.type, o.type_desc, o.create_date, o.modify_date, ( ( -- Concatenate together all the sections of code that make up a given object SELECT c2.[text] FROM syscomments c2 (nolock) WHERE c2.[id] = o.[object_id] ORDER BY colid FOR XML PATH(‘‘) , TYPE ).value(‘.‘, ‘nvarchar(max)‘) ) AS [txt] from sys.objects o ) SELECT cte.* /* -- These extend the potency of the view for doing code comparison. Since this view is only -- being posted as part of another suite of code, I have commmented them out. Uncomment if -- they would be useful to you in some way. , LEN([txt]) AS [txt_len] -- The name swap is to contend with cases where the object text may be the same but the name -- differs, as might be the case with an automatic constraint name. , CHECKSUM(REPLACE(txt, name, ‘##NAME##‘)) as [checksum] */ FROM cte -- WHERE [txt] IS NOT NULL -- Allow returing of objects w/o bodies GO -- Ensure it compiles select top 3 * from [VW_OBJ_TXT] GO
https://www.codeproject.com/Tips/667730/T-SQL-Search-SQL-Code
EXEC [dbo].[PR_FIND] ‘Sys_Role‘
原文：https://www.cnblogs.com/weifeng123/p/11114305.html