Database | Proc | Application | Created | Links |
sybsystemprocs | sp_merge_dup_inline_default ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Messages for "sp_merge_dup_inline_default" 4 ** 5 ** 17260, "Can't run %1! from within a transaction." 6 ** 17005, "The databases master, model, tempdb, sybsecurity, sybsystemprocs and mounted_sybsystemprocs cannot carry out this operation." 7 ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'." 8 ** 17006, "Database is modified and in single-user mode." 9 ** 17795, "System Administrator (SA) must reset it to multi-user mode with sp_dboption." 10 */ 11 12 13 14 create procedure sp_merge_dup_inline_default 15 @report_only varchar(30) = "YES", 16 @show_progress varchar(30) = "NO" 17 as 18 19 declare @dbname varchar(30) /* holds database name */ 20 declare @bitdesc varchar(30) /* bit description for the db */ 21 declare @msg varchar(1024) 22 declare @rollback int 23 declare @user_id int 24 declare @def_val varchar(255) 25 declare @def_id int 26 declare @def_name varchar(255) 27 declare @new_def_text varchar(300) 28 declare @tot_def_removed int 29 declare @tot_sharable_converted int 30 declare @unique_def int 31 declare @tot_def int 32 declare @cur_prog int 33 declare @prog_milestone int 34 declare @prog_secondary_milestone int 35 declare @dot_per_milestone int 36 declare @num_of_milestones int 37 declare @num_of_secondary_milestones int 38 39 /* 40 ** If we're in a transaction, disallow this 41 */ 42 if @@trancount > 0 43 begin 44 /* 45 ** 17260, "Can't run %1! from within a transaction." 46 */ 47 raiserror 17260, "sp_remoev_dup_inline_default" 48 return (1) 49 end 50 else 51 begin 52 set chained off 53 end 54 55 set transaction isolation level 1 56 57 set nocount on 58 set flushmessage on 59 60 /* check if user has sa role, proc_role will also do auditing 61 ** if required. proc_role will also print error message if required. 62 */ 63 64 if (proc_role("sa_role") = 0) 65 return (1) 66 67 /* 68 ** Get the database name we are in: 69 */ 70 select @dbname = db_name() 71 72 /* 73 ** Don't allow the names of master, tempdb, and model to be changed. 74 */ 75 if @dbname in ("master", "model", "tempdb", "sybsecurity", "sybsystemprocs", "mounted_sybsystemprocs") 76 begin 77 /* 78 ** 17005, "The databases master, model, tempdb, sybsecurity, 79 ** sybsystemprocs and mounted_sybsystemprocs cannot carry out this operation." 80 */ 81 raiserror 17005 82 return (1) 83 end 84 85 /* 86 ** Check single user bit (4096) 87 ** Database must be in single user mode to necessitate the removal of 88 ** existing default objects which could be more likely inuse otherwise. 89 */ 90 select @bitdesc = null 91 select @bitdesc = v.name 92 from master.dbo.spt_values v, master.dbo.sysdatabases d 93 where d.dbid = db_id(@dbname) 94 and v.type = "D" 95 and d.status & v.number = 4096 96 if @bitdesc is null 97 begin 98 /* 99 ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'." 100 */ 101 raiserror 17793, @dbname, "sp_merge_dup_inline_default" 102 return (1) 103 end 104 105 create table #defval_tab(uid int not null, inline_default varchar(255) null, dup_cnt int) 106 create unique index i1 on #defval_tab(uid, inline_default) 107 create table #dupdef_tab(id int) 108 create unique index i2 on #dupdef_tab(id) 109 110 IF @show_progress = 'YES' 111 BEGIN 112 PRINT 'Calculating...' 113 END 114 115 /* 116 ** locate all unique literal constant inline defaults. 117 */ 118 INSERT INTO #defval_tab(uid, inline_default, dup_cnt) 119 SELECT uid, inline_default, count(*) dup_cnt 120 FROM (SELECT O.uid, 121 rtrim(case when right (D.text, 1) = char(10) 122 then left (D.text, len(D.text) - 1) 123 else D.text 124 end) inline_default 125 FROM dbo.syscomments D, 126 dbo.sysobjects O, 127 dbo.sysprocedures P 128 WHERE D.id = O.id 129 and O.id = P.id 130 and O.type = 'D' 131 and P.type = 2 132 and P.sequence = 0 133 and P.number = 0 134 and P.status & 4096 = 4096 135 and D.number = 0 136 and D.colid = 1 137 and D.colid2 = 0 138 and D.texttype = 0 139 and (SELECT count(*) 140 FROM dbo.syscomments DD 141 WHERE DD.id = O.id) <= 1 142 ) T(uid, inline_default) 143 GROUP BY uid, inline_default 144 PLAN "(insert (group_hashing 145 (nested (nl_join (nl_join 146 (t_scan (table (O [dbo.sysobjects]))) 147 (i_scan csyscomments (table (D [dbo.syscomments])))) 148 (i_scan csysprocedures (table (P [dbo.sysprocedures])))) 149 (subq (scalar_agg (i_scan csyscomments (table (DD [dbo.syscomments]))))))))" 150 151 DELETE #defval_tab 152 WHERE inline_default is null 153 154 /* 155 ** The default value must be a literal constant in the form 156 ** of '[^']*', N'[^']*', "[^"]*", N"[^"]", {[+-]}[0-9]*[.][0-9]*, 157 ** in other words, all strings without escaped string delimiters 158 ** in-between and all numbers. The sharable inline default 159 ** feature actually allows more forms of literal constant such 160 ** as scientic number representation. But I'm here making this 161 ** SP simpler and will ignore those cases for now. 162 */ 163 DELETE #defval_tab 164 WHERE not ((substring(inline_default, 10, 1) = "'" 165 AND 166 right (inline_default, 1) = "'" 167 AND 168 charindex("'", substring(inline_default, 11, len(inline_default) - 11)) = 0) 169 OR 170 (substring(inline_default, 10, 2) = "N'" 171 AND 172 right (inline_default, 1) = "'" 173 AND 174 charindex("'", substring(inline_default, 12, len(inline_default) - 12)) = 0) 175 OR 176 (substring(inline_default, 10, 1) = '"' 177 AND 178 right (inline_default, 1) = '"' 179 AND 180 charindex('"', substring(inline_default, 11, len(inline_default) - 11)) = 0) 181 OR 182 (substring(inline_default, 10, 2) = 'N"' 183 AND 184 right (inline_default, 1) = '"' 185 AND 186 charindex('"', substring(inline_default, 12, len(inline_default) - 12)) = 0) 187 OR 188 (str_replace( 189 str_replace( 190 str_replace( 191 str_replace( 192 str_replace( 193 str_replace( 194 str_replace( 195 str_replace( 196 str_replace( 197 str_replace(inline_default, '0', ''), 198 '1', ''), 199 '2', ''), 200 '3', ''), 201 '4', ''), 202 '5', ''), 203 '6', ''), 204 '7', ''), 205 '8', ''), 206 '9', '') = "DEFAULT ") 207 OR 208 (str_replace( 209 str_replace( 210 str_replace( 211 str_replace( 212 str_replace( 213 str_replace( 214 str_replace( 215 str_replace( 216 str_replace( 217 str_replace(inline_default, '0', ''), 218 '1', ''), 219 '2', ''), 220 '3', ''), 221 '4', ''), 222 '5', ''), 223 '6', ''), 224 '7', ''), 225 '8', ''), 226 '9', '') = "DEFAULT +") 227 OR 228 (str_replace( 229 str_replace( 230 str_replace( 231 str_replace( 232 str_replace( 233 str_replace( 234 str_replace( 235 str_replace( 236 str_replace( 237 str_replace(inline_default, '0', ''), 238 '1', ''), 239 '2', ''), 240 '3', ''), 241 '4', ''), 242 '5', ''), 243 '6', ''), 244 '7', ''), 245 '8', ''), 246 '9', '') = "DEFAULT -") 247 OR 248 (str_replace( 249 str_replace( 250 str_replace( 251 str_replace( 252 str_replace( 253 str_replace( 254 str_replace( 255 str_replace( 256 str_replace( 257 str_replace(inline_default, '0', ''), 258 '1', ''), 259 '2', ''), 260 '3', ''), 261 '4', ''), 262 '5', ''), 263 '6', ''), 264 '7', ''), 265 '8', ''), 266 '9', '') = "DEFAULT .") 267 OR 268 (str_replace( 269 str_replace( 270 str_replace( 271 str_replace( 272 str_replace( 273 str_replace( 274 str_replace( 275 str_replace( 276 str_replace( 277 str_replace(inline_default, '0', ''), 278 '1', ''), 279 '2', ''), 280 '3', ''), 281 '4', ''), 282 '5', ''), 283 '6', ''), 284 '7', ''), 285 '8', ''), 286 '9', '') = "DEFAULT +.") 287 OR 288 (str_replace( 289 str_replace( 290 str_replace( 291 str_replace( 292 str_replace( 293 str_replace( 294 str_replace( 295 str_replace( 296 str_replace( 297 str_replace(inline_default, '0', ''), 298 '1', ''), 299 '2', ''), 300 '3', ''), 301 '4', ''), 302 '5', ''), 303 '6', ''), 304 '7', ''), 305 '8', ''), 306 '9', '') = "DEFAULT -.")) 307 308 SELECT @unique_def = count(*), 309 @tot_def = isnull(sum(dup_cnt), 0) 310 FROM #defval_tab 311 312 if @report_only != "NO" 313 begin 314 print ' ' 315 print '===============================================================' 316 print 'sp_merge_dup_inline_default is used to identify duplicate' 317 print 'inline default objects, subsequently to convert one of them' 318 print 'into sharable inline default object and remove the rest.' 319 print 'As the result, it will remove entries from sysobjects,' 320 print 'syscomments and sysprocedures. It will also update entries' 321 print 'in syscolumns, syscomments and sysprocedures.' 322 print ' ' 323 print 'Following is the current state of your inline default objects' 324 print 'found out by sp_merge_dup_inline_default and what it could' 325 print 'potentially do to them. By default, sp_merge_dup_inline_default' 326 print 'only reports the current state and this warning message. If you' 327 print 'really intend to carry out the changes, please rerun this' 328 print 'stored procedure using ' 329 print 'sp_merge_dup_inline_default @report_only = "NO"' 330 print ' ' 331 332 SELECT @msg = 'Database ' + @dbname + ' has about ' + ltrim(str(@unique_def)) + ' unique inline defaults' 333 print @msg 334 335 print 'If you convert them into sharable inline defaults, the rest of' 336 337 SELECT @msg = 'total ' + ltrim(str(@tot_def)) + ' duplicate defaults can be removed from the system catalogs.' 338 print @msg 339 340 print '===============================================================' 341 342 return (0) 343 end 344 345 SELECT @tot_def_removed = 0 346 SELECT @tot_sharable_converted = 0 347 SELECT @cur_prog = 0 348 SELECT @num_of_milestones = 0 349 SELECT @num_of_secondary_milestones = 0 350 351 /* 352 ** Decide how to show progress if needed. 353 ** It is only approximate and quite rough. Just to give some 354 ** visual feedback if needed. 355 */ 356 IF @unique_def > 64 357 BEGIN 358 SELECT @dot_per_milestone = 1 359 SELECT @prog_milestone = case when @unique_def % 64 = 0 360 then @unique_def / 64 361 else (@unique_def + 64) / 64 362 end 363 SELECT @num_of_secondary_milestones = 64 - (@unique_def / @prog_milestone) 364 END 365 ELSE 366 BEGIN 367 IF @unique_def > 0 368 BEGIN 369 SELECT @dot_per_milestone = 64 / @unique_def 370 SELECT @prog_milestone = 1 371 SELECT @num_of_secondary_milestones = 64 % (@unique_def * @dot_per_milestone) 372 END 373 END 374 IF @num_of_secondary_milestones != 0 375 BEGIN 376 SELECT @prog_secondary_milestone = (@unique_def / @prog_milestone) / @num_of_secondary_milestones 377 END 378 ELSE 379 BEGIN 380 SELECT @prog_secondary_milestone = 0 381 END 382 383 IF @show_progress = 'YES' 384 BEGIN 385 PRINT 'Converting...' 386 387 SELECT @msg = '[#' + replicate(' ', 64) + ']' 388 PRINT @msg 389 END 390 391 /* 392 ** we will carry out the conversion in a transactional manner 393 ** for each unique default. Once the work for one unique default 394 ** is done, we commit and move onto the next one. 395 */ 396 DECLARE defval_cur CURSOR FOR 397 SELECT uid, inline_default 398 FROM #defval_tab 399 ORDER BY dup_cnt 400 FOR READ ONLY 401 402 OPEN defval_cur 403 404 /* 405 ** go through each one and remove the duplicate ones. 406 */ 407 FETCH defval_cur INTO @user_id, @def_val 408 WHILE @@sqlstatus = 0 409 BEGIN 410 IF @cur_prog = @prog_milestone 411 BEGIN 412 SELECT @num_of_milestones = @num_of_milestones + 1 413 SELECT @cur_prog = 0 414 415 IF @show_progress = 'YES' 416 BEGIN 417 SELECT @msg = '[#' + replicate('#', @dot_per_milestone * @num_of_milestones) 418 IF @num_of_secondary_milestones > 0 419 and 420 @num_of_milestones / @prog_secondary_milestone > 0 421 BEGIN 422 SELECT @msg = @msg + replicate('#', @num_of_milestones / @prog_secondary_milestone) 423 END 424 SELECT @msg = @msg + replicate(' ', 64) + ']' 425 SELECT @msg = left (@msg, 66) + ']' 426 PRINT @msg 427 END 428 END 429 430 /* locate all duplicate default objects with the same @def_val */ 431 INSERT INTO #dupdef_tab 432 SELECT D.id 433 FROM dbo.syscomments D, dbo.sysobjects O, dbo.sysprocedures P 434 WHERE rtrim(case when right (D.text, 1) = char(10) 435 then left (D.text, len(D.text) - 1) 436 else D.text 437 end) = @def_val 438 and D.id = O.id 439 and O.type = 'D' 440 and O.uid = @user_id 441 and O.id = P.id 442 and P.type = 2 443 and P.sequence = 0 444 and P.number = 0 445 and P.status & 4096 = 4096 446 and D.number = 0 447 and D.colid = 1 448 and D.colid2 = 0 449 and (SELECT count(*) 450 FROM dbo.syscomments DD 451 WHERE DD.id = O.id) <= 1 452 PLAN "(insert (nl_join (nl_join (nested 453 (scan (table (D [dbo.syscomments]))) 454 (subq (scalar_agg (i_scan csyscomments (table (DD [dbo.syscomments])))))) 455 (i_scan csysobjects (table (O [dbo.sysobjects])))) 456 (i_scan csysprocedures (table (P [dbo.sysprocedures])))))" 457 458 /* pick one of them to be converted to a sharable one */ 459 SELECT TOP 1 @def_id = D.id, @def_name = O.name 460 FROM #dupdef_tab D, dbo.sysobjects O 461 WHERE D.id = O.id 462 and charindex("[", O.name) = 0 463 and charindex("]", O.name) = 0 464 and datalength("create default [" + rtrim(O.name) + "] as " + right (@def_val, len(@def_val) - 9) + char(10)) <= 255 465 466 If @@rowcount != 1 467 BEGIN 468 SELECT @cur_prog = @cur_prog + 1 469 TRUNCATE TABLE #dupdef_tab 470 FETCH defval_cur INTO @user_id, @def_val 471 CONTINUE 472 END 473 474 /* the rest will be removed from system catalogs */ 475 DELETE #dupdef_tab 476 WHERE id = @def_id 477 478 /* sharable inline default has a slightly different text representation */ 479 SELECT @new_def_text = "create default [" + rtrim(@def_name) + "] as " + right (@def_val, len(@def_val) - 9) + char(10) 480 481 UPDATE INDEX STATISTICS #dupdef_tab 482 483 SELECT @rollback = 1 484 BEGIN TRAN shrink_db 485 486 /* first change existing columns to only use the sharable one */ 487 UPDATE syscolumns 488 SET cdefault = @def_id 489 FROM syscolumns 490 WHERE cdefault in (SELECT id from #dupdef_tab) 491 492 IF @@error = 0 493 BEGIN 494 /* next delete the duplicate entries from sysprocedures */ 495 DELETE sysprocedures 496 WHERE id in (select id from #dupdef_tab) 497 PLAN "(delete (nl_join (distinct_sorted (i_scan i2 #dupdef_tab)) 498 (i_scan csysprocedures sysprocedures)))" 499 500 IF @@error = 0 501 BEGIN 502 /* ... also from syscomments */ 503 DELETE syscomments 504 WHERE id in (select id from #dupdef_tab) 505 506 IF @@error = 0 507 BEGIN 508 /* ... also from sysobjects. */ 509 DELETE sysobjects 510 WHERE id in (select id from #dupdef_tab) 511 512 IF @@error = 0 513 BEGIN 514 /* finally convert the chosen one into sharable inline default */ 515 UPDATE sysprocedures 516 SET status = status & ~ 4096 517 FROM sysprocedures 518 WHERE id = @def_id 519 PLAN "(update (i_scan csysprocedures sysprocedures))" 520 521 IF @@error = 0 522 BEGIN 523 UPDATE syscomments 524 SET status = status | 8, 525 text = @new_def_text 526 FROM syscomments 527 WHERE id = @def_id 528 PLAN "(update (i_scan csyscomments syscomments))" 529 530 IF @@error = 0 531 BEGIN 532 commit tran shrink_db 533 set @rollback = @@error 534 END 535 END 536 END 537 END 538 END 539 END 540 541 /* 542 ** If any of the above steps failed, we need to rollback all the changes 543 ** made for this unique default, which will also lead to the ending of this 544 ** SP run. However the ealier committed changes (if any) for different 545 ** unique defaults are already completed. If user immediately re-runs 546 ** the SP now, the conversion will "resume" from the failed unique default. 547 ** Typical reasons for failure are out of system resource (such as locks) or 548 ** manual abort. 549 */ 550 IF @rollback != 0 551 BEGIN 552 ROLLBACK TRAN shrink_db 553 BREAK 554 END 555 ELSE 556 BEGIN 557 SELECT @tot_def_removed = @tot_def_removed + count(*) 558 FROM #dupdef_tab 559 560 SELECT @tot_sharable_converted = @tot_sharable_converted + 1 561 END 562 563 SELECT @cur_prog = @cur_prog + 1 564 TRUNCATE TABLE #dupdef_tab 565 FETCH defval_cur INTO @user_id, @def_val 566 END 567 568 CLOSE defval_cur 569 DEALLOCATE CURSOR defval_cur 570 571 IF @show_progress = 'YES' 572 BEGIN 573 SELECT @msg = '[#' + replicate('#', 64) + ']' 574 PRINT @msg 575 PRINT ' ' 576 END 577 578 IF @tot_sharable_converted > 0 579 BEGIN 580 SELECT @msg = 'Total ' + ltrim(str(@tot_def_removed)) + ' duplicate defaults are removed and ' + ltrim(str(@tot_sharable_converted)) + ' defaults' 581 print @msg 582 print 'are converted to sharable inline defaults.' 583 584 /* 585 ** 17006, "Database is modified and in single-user mode." 586 */ 587 exec sp_getmessage 17006, @msg output 588 print @msg 589 590 /* 591 ** 17795, "System Administrator (SA) must reset it to multi-user mode with sp_dboption." 592 */ 593 exec sp_getmessage 17795, @msg output 594 print @msg 595 END 596 ELSE 597 BEGIN 598 print 'Database is not modified. Please try it later if duplicate inline ' 599 print 'defaults do exist and the current resource limitation is preventing ' 600 print 'this conversion process.' 601 END 602 603 return (0) 604
exec sp_procxmode 'sp_merge_dup_inline_default', 'AnyMode' go Grant Execute on sp_merge_dup_inline_default to public go
DEPENDENCIES |
PROCS AND TABLES USED calls proc sybsystemprocs..sp_getmessage ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() reads table master..syslanguages (1) ![]() writes table sybsystemprocs..syscolumns ![]() reads table master..spt_values (1) ![]() read_writes table sybsystemprocs..syscomments ![]() read_writes table sybsystemprocs..sysprocedures ![]() read_writes table sybsystemprocs..sysobjects ![]() reads table master..sysdatabases (1) ![]() read_writes table tempdb..#dupdef_tab (1) read_writes table tempdb..#defval_tab (1) |