| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_ddlgen_database | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_ddlgen_database 4 ** 5 ** Entry-point for generating DDL for a database. Currently all we support 6 ** is the basic create/alter database syntax. Subsequent sp_dboption etc. 7 ** are not [ to be ] supported. The expectation is that the DDLGen tool 8 ** will provide those value-adds. 9 ** 10 ** Parameters: 11 ** @dbname - Name of db to generate DDL for. Supports wild-cards. 12 ** @trace - Trace options (internal use.) 13 ** 14 ** Trace Levels: 15 ** ------------ 16 ** 1 - Basic tracing of code flow. 17 ** 2 - Trace alter db SQL generation. 18 ** 1192 - Pretend that this is a 119x server, and ignore the code/data 19 ** pieces that exist in 12.5.x, or later, but not in 11.9.x. 20 ** 120 - Pretend that this is a 120.x server, and ignore the code/data 21 ** pieces that exist in 12.5.x, or later, but not in 12.0.x. 22 ** [ By running in this older-server mode, you can test that 23 ** this sproc will correctly collapse multiple alter dbs into 24 ** one when possible (ignoring 'crdate'), and will generate 25 ** the syntax corectly without unit-specifiers. ] 26 { 27 */ 28 create procedure sp_ddlgen_database( 29 @dbname varchar(255) = NULL 30 , @trace int = 0 31 ) as 32 begin -- { 33 declare @newline char(1) 34 , @tabchar char(1) 35 , @retval int 36 , @objtype varchar(10) 37 , @objtype_lower varchar(10) 38 , @prev_dbname varchar(30) 39 , @devname varchar(255) 40 , @segment varchar(12) -- of current disk piece 41 , @prev_segment varchar(20) -- of previous disk piece 42 , @lstart int 43 , @size_pages int 44 , @status3 int -- sysdatabases.status3 45 , @devstat int -- sysdevices.status 46 , @devstat2 int -- sysdevices.status2 47 , @size_str varchar(20) -- size as a string. 48 , @size_comments varchar(255) -- size in comments (and other 49 -- diag info if tracing is ON). 50 , @size_mb varchar(10) 51 , @prefix varchar(10) 52 53 , @create_sql varchar(6) 54 , @alterdb_sql varchar(15) 55 , @override_sql varchar(13) 56 , @local_systempdb_ph varchar(18) 57 , @local_usrtempdb_ph varchar(11) 58 , @global_usrtempdb_ph varchar(18) 59 , @tempdb_ph varchar(30) -- used to point to these above. 60 , @imdb_sql varchar(9) 61 , @imdb_ph varchar(30) 62 , @archive_sql varchar(30) 63 , @durability_no_recovery_sql varchar(13) 64 , @durability_at_shutdown_sql varchar(13) 65 , @durability_full_sql varchar(6) 66 , @dmllogging_minimal_sql varchar(7) 67 , @dmllogging_full_sql varchar(4) 68 69 , @data_compression_row_sql varchar(3) 70 , @data_compression_page_sql varchar(4) 71 , @data_compression_none_sql varchar(4) 72 73 , @archivedb tinyint 74 , @usertempdb tinyint -- is it a user-created tempdb. 75 , @templatedbname varchar(30) -- print the template database name 76 , @durability varchar(13) 77 , @dmllogging varchar(9) 78 , @data_compression varchar(4) 79 , @lob_compression int 80 , @inrow_lob_length int 81 , @instancename varchar(30) -- used to print the instance 82 -- of which the current database 83 -- is local system temporary 84 -- database. 85 , @durability_status int --sysdatabases.durability 86 , @dmllogging_status int --sysdatabases.status4 87 , @durability_no_recovery_status int 88 , @durability_at_shutdown_status int 89 , @durability_full_status int 90 , @dmllogging_minimal_status int 91 92 , @data_compression_status int --sysdatabases.status4 93 , @data_compression_row_status int 94 , @data_compression_page_status int 95 96 97 98 99 -- # of dbids for which we didn't generate the right #s of rows 100 -- in the #temp table when compared to the # of rows that exist 101 -- for that db in sysusages. 102 -- 103 , @count_failed_dbids int 104 105 -- To account for >2k logical page sizes 106 , @numpgsmb float 107 108 109 -- Various status bits in catalogs for re-use. 110 , @stat3_tempdb int 111 , @stat3_archivedb int 112 , @stat4_inmem_db int 113 , @stat4_explicit_no_rec int 114 115 , @inmem_db tinyint 116 , @explicit_no_rec tinyint 117 118 -- Version sub-string to check against running server. 119 , @ase_1192_str varchar(30) 120 , @ase_120x_str varchar(30) 121 , @ase_150x_str varchar(30) 122 , @ase_155x_str varchar(30) 123 , @ase_157x_str varchar(30) 124 125 -- Are we running in a 12.5x or later server? 126 , @ase_125x_or_gt tinyint 127 , @ase_150x_or_gt tinyint 128 , @ase_155x_or_gt tinyint 129 , @ase_157x_or_gt tinyint 130 131 , @sqlstmt varchar(255) 132 , @indent varchar(32) 133 , @archivedb_comm varchar(255) 134 135 , @comment char(3) 136 , @pages char(6) 137 , @crdate datetime 138 , @prev_crdate datetime -- of previous disk piece 139 , @use_crdate tinyint -- whether server supports this 140 , @override tinyint -- setting of current disk piece 141 , @with_override tinyint -- whether we need to set this 142 -- for this set of disk pieces. 143 , @with_dur_and_dmllog tinyint 144 -- whether we need to set this 145 -- for the database. 146 , @with_dcomp_and_IRL tinyint 147 -- whether we need to set this 148 -- for the database. 149 150 , @units_ok tinyint -- does server support 151 -- unit specifiers. 152 153 select @newline = char(10) 154 , @tabchar = char(9) 155 , @comment = "-- " 156 , @pages = " pages" 157 , @objtype = "DATABASE" 158 , @indent = space(@@nestlevel * 2) 159 , @retval = 0 160 161 select @objtype_lower = lower(@objtype) 162 163 -- SQL fragments that we will reuse. 164 , @create_sql = "CREATE" 165 , @alterdb_sql = "ALTER DATABASE" 166 , @override_sql = "WITH OVERRIDE" 167 , @imdb_sql = " INMEMORY" 168 , @local_systempdb_ph 169 = " SYSTEM TEMPORARY " 170 , @local_usrtempdb_ph 171 = " TEMPORARY " 172 , @global_usrtempdb_ph 173 = " GLOBAL TEMPORARY " 174 , @archive_sql = " TEMPLATE SQL FOR ARCHIVE " 175 176 -- Version parsing related variables. 177 , @ase_1192_str = "%Enterprise/11.9%" 178 , @ase_120x_str = "%Enterprise/12.0%" 179 , @ase_150x_str = "%Enterprise/15.0%" 180 , @ase_155x_str = "%Enterprise/15.5%" 181 , @ase_157x_str = "%Enterprise/15.7%" 182 183 -- Build special-use comment for archive dbs. 184 -- 185 , @archivedb_comm = @newline + 186 + "-- This is a template DDL for an archive database. Fill in the logical" 187 + @newline 188 + "-- device names based on the devices existing in your installation," 189 + @newline 190 + "-- and uncomment the WITH OVERRIDE clause, as necessary." 191 192 -- 125x was when ASE started support unit-specifiers for 193 -- DB DDL. So assume the server is >= 125x by default so we 194 -- can always generate the new DDL with unit-specifiers. 195 -- Likewise, 12.5 onwards, sysusages.crdate existed. 196 -- 197 , @ase_125x_or_gt = 1 -- Assume it's 12.5.x or later till 198 -- we find out otherwise. 199 200 , @ase_150x_or_gt = 0 -- Assume it's *not* 15.0.x or later 201 -- till we find out otherwise. 202 203 , @ase_155x_or_gt = 0 -- Assume it's *not* 15.5.x or later 204 -- till we find out otherwise. 205 206 , @ase_157x_or_gt = 0 -- Assume it's *not* 15.7.x or later 207 -- till we find out otherwise. 208 209 -- Initialize 'prev' values that are used later for comparing 210 -- whether disk piece info changed across rows. 211 -- 212 , @prev_dbname = NULL 213 , @prev_segment = NULL 214 , @prev_crdate = NULL 215 216 -- Assume server does have this column. (We can't do matching 217 -- based on 'sysusages.crdate', till we are at 12.5x. or later.) 218 -- 219 , @use_crdate = 0 220 221 222 , @override = 0 -- Assume no WITH OVERRIDE 223 , @usertempdb = 0 -- Assume it's not a user tempdb 224 , @with_override = 0 225 , @with_dur_and_dmllog = 0 226 , @with_dcomp_and_IRL = 0 227 228 -- From 15.5 or later, the following variables are introduced 229 -- Initialize the following variables 230 -- 231 , @templatedbname = NULL 232 , @durability = NULL 233 , @dmllogging = NULL 234 , @durability_no_recovery_sql = "NO_RECOVERY" 235 , @durability_at_shutdown_sql = "AT_SHUTDOWN" 236 , @durability_full_sql = "FULL" 237 , @dmllogging_minimal_sql = "MINIMAL" 238 , @dmllogging_full_sql = "FULL" 239 240 -- From 15.7 or later, the following variables are introduced 241 -- Initialize the following variables 242 -- 243 , @data_compression = NULL 244 , @data_compression_row_sql = "ROW" 245 , @data_compression_page_sql = "PAGE" 246 , @data_compression_none_sql = "NONE" 247 248 249 -- Initialize various system catalog status bits 250 , @stat3_archivedb = 4194304 251 , @durability_no_recovery_status = 6 252 , @durability_at_shutdown_status = 5 253 , @durability_full_status = 1 254 , @dmllogging_minimal_status = 256 255 , @stat4_inmem_db = 4096 -- It is an 'inmemory' db 256 , @stat4_explicit_no_rec = 8192 -- Tempdb was created with 257 -- explicit NO_RECOVERY clause. 258 , @inmem_db = 0 259 , @explicit_no_rec = 0 -- Assume all tempdbs were not 260 -- created / altered with 261 -- explicit NO_RECOVERY option. 262 , @data_compression_row_status = 33554432 263 , @data_compression_page_status = 16777216 264 265 266 select @stat3_tempdb = number 267 from master.dbo.spt_values 268 where type = "D3" 269 and name = "TEMPDB STATUS MASK" 270 271 select @numpgsmb = (1048576. / v.low) 272 from master.dbo.spt_values v 273 where v.number = 1 274 and v.type = "E" 275 276 if (@objtype IS NULL) 277 select @objtype = "DATABASE" 278 279 -- Validate that the db exists, first, if only one was requested. 280 if (charindex("%", @dbname) = 0) and (db_id(@dbname) IS NULL) 281 begin 282 raiserror 18322, @dbname 283 return 1 284 end 285 286 -- Identify the server version we are running on. 287 -- 288 if (@@version like @ase_1192_str) or (@@version like @ase_120x_str) 289 begin 290 select @ase_125x_or_gt = 0 291 end 292 else if (@@version like @ase_150x_str) 293 begin 294 select @ase_150x_or_gt = 1 295 end 296 else if (@@version like @ase_155x_str) 297 begin 298 select @ase_155x_or_gt = 1 299 end 300 else if (@@version like @ase_157x_str) 301 begin 302 -- 15.7 subsumes 15.5 DDL syntax, too. So turn ON the 303 -- boolean for that release-checking, too. 304 -- 305 select @ase_157x_or_gt = 1 306 , @ase_155x_or_gt = 1 307 end 308 309 310 -- Support masquerading as an older server. 311 -- 312 if (@trace in (1192, 120)) 313 begin 314 select @ase_125x_or_gt = 0 315 , @ase_150x_or_gt = 0 316 , @ase_155x_or_gt = 0 317 , @ase_157x_or_gt = 0 318 end 319 320 -- Some features are only usable 12.5.x onwards: 321 -- . unit-specifiers 322 -- . 'sysusages.crdate' 323 -- 324 select @units_ok = @ase_125x_or_gt 325 , @use_crdate = @ase_125x_or_gt 326 327 -- ################################################################### 328 -- Stash away the segment info into a #temp table for processing. 329 -- 330 select u.dbid 331 , dbname = d.name 332 333 -- Remember whether this usages entry is for an archivedb. 334 , archivedb = 0 335 , d.status3 336 , u.lstart 337 , u.segmap 338 , segment = case 339 WHEN ((u.segmap & 4) = 0) then 'data only' 340 WHEN (u.segmap = 4) then 'log only' 341 ELSE 'data and log' 342 end 343 , size = convert(int, u.size) 344 -- , sizeMb = ltrim(rtrim(str(u.size/512.0))) + 'M' 345 , sizeMb = ltrim(rtrim(str(u.size / @numpgsmb))) + 'M' 346 , vdevno = u.vdevno -- use this directly in 15.0 onwards. 347 , override = 0 -- whether this is a shared device that 348 -- needs WITH OVERRIDE clause to be 349 -- generated. 350 , devname = v.name 351 , devstat = v.status 352 , devstat2 = v.status2 353 354 -- The sysusages entries fabricated from the dump for an 355 -- archive db don't have the original 'crdate' (if exists) 356 -- from the db dump. Fabricate current date, as a placeholder. 357 -- 358 , crdate = getdate() 359 into #seginfo 360 from master.dbo.sysusages u 361 , master.dbo.sysdevices v 362 , master.dbo.sysdatabases d 363 where 1 = 1 364 and d.name like @dbname -- support for '%mydb%' 365 and d.dbid = u.dbid 366 and ((d.status3 & @stat3_archivedb) != @stat3_archivedb) 367 -- skip archive dbs for now 368 -- We'll get them a bit later. 369 370 and u.vdevno = v.vdevno -- Use this for 15.0 onwards. 371 372 and (((v.status & 2) = 2) -- for physical devices 373 or ((v.status2 & 8) = 8)) -- for cache devices 374 375 -- ################################################################### 376 -- Validate that the above SQL captured exactly one row for each 377 -- sysusages entry for a db. If not, we will be generating wrong 378 -- data, as there is some cross-product join happening. 379 -- If the validation fails, error out. [ This came about as during 380 -- testing some wrong WHERE clause was used in 15.0.x from a version 381 -- of this sproc meant for 12.5.x. Add this validation to trap 382 -- future errors.] 383 -- 384 select @count_failed_dbids = (select count(1) 385 from (select dbid, numrows = count(*) 386 from #seginfo group by dbid) V1 387 , (select dbid, numrows = count(*) 388 from master.dbo.sysusages group by dbid) V2 389 where V1.dbid = V2.dbid 390 and V1.numrows != V2.numrows 391 ) 392 393 if (@count_failed_dbids != 0) 394 begin 395 print "Found %1! dbids with wrong number of rows cached in '%2!' v/s the rows in '%3!'" 396 , @count_failed_dbids 397 , '#seginfo' 398 , 'master.dbo.sysusages' 399 400 -- Display the dbids for which we failed, if tracing is ON. 401 -- RESOLVE: This code will probably not work in 12.0, or 402 -- 11.9.x. Need to maintain a different version of this 403 -- sproc for those codelines. 404 -- 405 if (@trace = 1) 406 begin 407 select V1.dbid 408 , dbname = db_name(V1.dbid) 409 , expected_in_sysusages = V2.numrows 410 , actual_in_#seginfo = V1.numrows 411 , status = case V1.numrows 412 when V2.numrows 413 then "ok" 414 else "not ok" 415 end 416 into #failed_dbids 417 from (select dbid, numrows = count(*) 418 from #seginfo group by dbid) V1 419 , (select dbid, numrows = count(*) 420 from master.dbo.sysusages group by dbid) V2 421 where V1.dbid = V2.dbid 422 423 -- Skip this clause, so that we can see all rows. 424 -- and V1.numrows != V2.numrows 425 426 -- See the 'not ok' rows at the top. 427 exec sp_autoformat @fulltabname = #failed_dbids 428 , @orderby = "order by status asc" 429 end 430 431 return @count_failed_dbids 432 end 433 434 -- exec sp_autoformat #seginfo 435 436 -- ################################################################### 437 -- Of the dbs named by the user by the @dbname argument, if there is 438 -- even one archive db, fork off control to a sub-proc that knows how 439 -- to gather and process sysusages entries for archive dbs. 440 -- 441 if exists (select 1 from master.dbo.sysdatabases d 442 where d.name like @dbname 443 and ((d.status3 & @stat3_archivedb) = @stat3_archivedb)) 444 begin 445 -- Reuse @devname to raise an error message. 446 select @devname = """sp_ddlgen 'database', " + @dbname + '"' 447 raiserror 19475, @devname 448 return 1 449 end 450 451 452 -- ################################################################### 453 -- Depending on the version of ASE, we might / not have 'crdate' 454 -- column in sysusages. If we do, copy that into the #temp table, 455 -- using execute immediate. Seems straightforward, but there is an 456 -- issue here if archive dbs are involved. In that case, master.. 457 -- sysusages has only a 'stub' entry for the db, whereas the #temp 458 -- table would have been populated with possibly multiple rows from 459 --..sysaltusages. In that case, we can't do a row-to-row 460 -- update from master..sysusages.crdate to #seginfo.crdate as there 461 -- won't be matching rows in master..sysusages. 462 -- 463 if (@ase_125x_or_gt = 1) 464 begin 465 if (@trace = 1) 466 begin 467 -- The UPDATE below sometimes fails. Run the query 468 -- to see what the actual results are like, using 469 -- data from the temp table. 470 -- 471 select u.dbid, u.crdate 472 from master.dbo.sysusages u, 473 #seginfo t 474 where u.dbid = t.dbid 475 and u.lstart = t.lstart 476 477 print "%1!---- End Trace update ----%2!" 478 , @indent, @newline 479 if (@retval != 0) 480 return @retval 481 end 482 483 -- First only update for non-archive db rows. 484 -- 485 update #seginfo 486 set crdate = (select u.crdate 487 from master.dbo.sysusages u 488 where u.dbid = t.dbid 489 and u.lstart = t.lstart) 490 from #seginfo t 491 where t.archivedb = 0 492 493 select @retval = @@error 494 495 if (@retval != 0) 496 begin 497 if (@trace = 1) 498 print "Error updating #seginfo.crdate using sysusages.crdate" 499 500 return @retval 501 end 502 end 503 504 -- ################################################################### 505 -- Go back and check each disk piece to see 506 -- 1) if it has data fragment created on the device when the device was 507 -- a log-only device. 508 -- OR 509 -- 2) if it has log fragment created on the device when the device was 510 -- a data-only device. 511 -- If so, the user would have had to create this with WITH OVERRIDE 512 -- clause. Note that here, we will re-gen the DDL accordingly. 513 -- -- 514 -- [ NOTE: This will update 'override' to 1 for all usages entries 515 -- for an archive db, as the 'vdevno' is the same for all entries. 516 -- That is ok, here as then we comment out the WITH OVERRIDE clause for 517 -- the DDL for an archive db. 518 -- ##################################################################### 519 -- The condition in the following update query to set @override bit 520 -- for the current disk piece to 1, is based on the fact that 521 -- 'with override'clause is needed only when 522 -- 1) The current disk piece is of either 'data only' or 'log only' 523 -- types. 524 -- AND 525 -- 2) There exists at least one disk piece on the same device as that 526 -- of the current disk piece such that the other disk piece was 527 -- created before the current disk piece. 528 -- (We need this condition to ensure that we do *not* generate the 529 -- WITH OVERRIDE clause for the first piece of a db on any device.) 530 -- AND 531 -- 3) There exists no disk piece preceding the current one such that the 532 -- preceding one is of either 'data and log' type or of the same type 533 -- as that of the current diskpiece. 534 -- 535 -- . If there exists a prior piece on the same device of type 536 -- "data and log", it means all its subsequent pieces would not 537 -- have needed the WITH OVERRIDE clause. 538 -- 539 -- . If there exists a prior piecce on the same device of the 540 -- same type as the current one, it means that prior piece 541 -- would have been created with WITH OVERRIDE clause. Hence, 542 -- all its subsequent pieces of the same segment type will 543 -- not have needed the WITH OVERRIDE clause; hence, we can 544 -- skip generating it here. 545 546 update #seginfo 547 set override = 1 548 from #seginfo o -- outer table 549 where segment IN ("data only", "log only") 550 and exists (select 1 from #seginfo i1 -- inner table 551 where i1.lstart < o.lstart 552 and i1.dbid = o.dbid 553 and i1.vdevno = o.vdevno) 554 and not exists (select 1 from #seginfo i2 -- inner table 555 556 -- ignore this very disk piece from the outer table, 557 -- and search for other disk pieces with a lower 558 -- lstart (i.e. the ones that were created -before- 559 -- the outer disk piece. 560 -- 561 where i2.lstart < o.lstart 562 and i2.dbid = o.dbid 563 and i2.vdevno = o.vdevno 564 and (i2.segment = "data and log" 565 or i2.segment = o.segment)) 566 567 declare diskcur cursor for 568 select dbname, devname, segment, size, sizeMb, override, crdate 569 , status3 570 , devstat 571 , devstat2 572 , lstart 573 , archivedb 574 from #seginfo 575 order by dbid, lstart asc 576 for read only 577 578 select @retval = @@error 579 if (@retval != 0) 580 return @retval 581 582 open diskcur 583 select @retval = @@error 584 if (@retval != 0) 585 return @retval 586 587 while (1 = 1) 588 begin -- { 589 fetch diskcur into @dbname 590 , @devname 591 , @segment 592 , @size_pages 593 , @size_mb 594 , @override 595 , @crdate 596 , @status3 597 , @devstat 598 , @devstat2 599 , @lstart 600 , @archivedb 601 602 select @retval = @@error 603 if (@@sqlstatus != 0) OR (@retval != 0) 604 break 605 606 -- We are beginning the processing for sysusages entries 607 -- for a new database. Initialize the SQL fragments... 608 -- 609 if (@prev_dbname != @dbname) 610 begin -- { 611 612 -- If a database previous to the current one existed 613 -- (That is, in case of multiple database input to 614 -- ddlgen using wild card) 615 -- 616 if (@prev_dbname IS NOT NULL) 617 begin 618 -- Handle the case that the very last disk 619 -- piece of previous database might need this 620 -- clause. 621 -- 622 exec sp_ddlgen_db_print_with_clause 623 @with_override output 624 , @with_dur_and_dmllog output 625 , @with_dcomp_and_IRL output 626 , @durability 627 , @dmllogging 628 , @data_compression 629 , @lob_compression 630 , @inrow_lob_length 631 , @archivedb 632 end 633 634 else if (@prev_dbname IS NULL) 635 begin 636 print "USE master%1!go%2!", @newline, @newline 637 end 638 639 -- Reset for every db that we'll process in a loop. 640 /* 641 ** If it is a temporary database other than 'tempdb' 642 ** add the keywords that reflect the type of the 643 ** database accordingly ([system | global] temporary). 644 */ 645 if ((@status3 & @stat3_tempdb) != 0) 646 begin 647 -- The database is a temporary database; start 648 -- with the default phrase (with the TEMPORARY 649 -- keyword) 650 -- 651 select @tempdb_ph = @local_usrtempdb_ph 652 653 if (@@clustermode = 'shared disk cluster') 654 begin 655 -- In case of sdc, there are a few 656 -- variations as shown in the switch 657 -- case. 658 -- 659 select @tempdb_ph = 660 case (@status3 & @stat3_tempdb) 661 when (select number 662 from master.dbo.spt_values 663 where name = 664 'local system temp db') 665 then @local_systempdb_ph 666 when (select number 667 from master.dbo.spt_values 668 where name = 669 'global user temp db') 670 then @global_usrtempdb_ph 671 else @local_usrtempdb_ph 672 end 673 674 end 675 end 676 677 else if ((@status3 & @stat3_archivedb) = @stat3_archivedb) 678 begin 679 select @tempdb_ph = @archive_sql 680 end 681 682 else 683 select @tempdb_ph = "" 684 685 /* 686 ** If it is a inmemory database, 687 ** add the keywords that reflect the type of the 688 ** database accordingly (inmemory). 689 */ 690 if (@ase_155x_or_gt = 1) 691 begin -- { 692 if (@@clustermode = 'smp') 693 begin 694 select @with_dur_and_dmllog = 1 695 end 696 697 -- Extract the durability from catalogs: 698 -- First find the durability as we see it in 699 -- the catalogs. Also note if it's a user 700 -- created tempdb. 701 -- 702 select @durability_status = durability 703 704 -- Is it a user-created temp db? 705 , @usertempdb = case (status3 706 & @stat3_tempdb) 707 when @stat3_tempdb 708 then 1 709 else 0 710 end 711 712 -- Is it a tempdb created with explicit 713 -- NO_RECOVERY durability? 714 -- 715 , @explicit_no_rec 716 = case (status4 & @stat4_explicit_no_rec) 717 when @stat4_explicit_no_rec 718 then 1 719 else 0 720 end 721 722 -- Is it an in-memory db? 723 , @inmem_db 724 = case (status4 & @stat4_inmem_db) 725 when @stat4_inmem_db 726 then 1 727 else 0 728 end 729 730 from master.dbo.sysdatabases 731 where name = @dbname 732 733 select @durability = 734 case (@durability_status) 735 when @durability_no_recovery_status 736 then @durability_no_recovery_sql 737 when @durability_at_shutdown_status 738 then @durability_at_shutdown_sql 739 else @durability_full_sql 740 end 741 742 743 -- Next, for the system tempdb, or for any 744 -- user-created tempdb, check if the db was 745 -- created / altered with the explicit 746 -- NO_RECOVERY durability. If not, reset the 747 -- durability to NULL. (Do not do this for 748 -- in-memory dbs which *need* to be always 749 -- created with the NO_RECOVERY clause.) 750 -- This will eventually suppress generating 751 -- the WITH DURABILITY clause in the DDL. 752 -- 753 if (((@dbname = 'tempdb') 754 or (@usertempdb = 1)) 755 and (@explicit_no_rec = 0) 756 and (@inmem_db = 0) 757 ) 758 begin 759 select @durability = NULL 760 end 761 762 select @dmllogging_status = status4 763 from master.dbo.sysdatabases 764 where name = @dbname 765 766 select @dmllogging = 767 case (@dmllogging_status 768 & @dmllogging_minimal_status) 769 when @dmllogging_minimal_status 770 then @dmllogging_minimal_sql 771 else @dmllogging_full_sql 772 end 773 774 if (@devstat2 & 8 = 8) 775 begin 776 select @imdb_ph = @imdb_sql 777 778 -- Don't forget to add: 779 -- 'USE dbname AS TEMPLATE' 780 -- clause for the database if it is an 781 -- IMDB db. Currently, we don't support 782 -- template clause 783 -- for inmemory temp DB 784 -- 785 if ((@status3 & @stat3_tempdb) = 0) 786 begin 787 select @templatedbname = def_remote_loc 788 from master.dbo.sysdatabases 789 where name = @dbname 790 end 791 end 792 793 if (@devstat2 & 8 != 8) 794 select @imdb_ph = NULL 795 796 if ((@devstat & 2 = 2) 797 and (@durability_status 798 & @durability_no_recovery_status 799 = @durability_no_recovery_status)) 800 begin 801 select @templatedbname = def_remote_loc 802 from master.dbo.sysdatabases 803 where name = @dbname 804 end 805 end -- } 806 807 if (@ase_157x_or_gt = 1) 808 begin -- { 809 select @with_dcomp_and_IRL = 1 810 811 select @data_compression_status = status4 812 from master.dbo.sysdatabases 813 where name = @dbname 814 815 if ((@data_compression_status 816 & @data_compression_row_status) 817 = @data_compression_row_status) 818 begin 819 select @data_compression 820 = @data_compression_row_sql 821 end 822 823 else if ((@data_compression_status 824 & @data_compression_page_status) 825 = @data_compression_page_status) 826 begin 827 select @data_compression 828 = @data_compression_page_sql 829 end 830 831 else 832 begin 833 select @data_compression 834 = @data_compression_none_sql 835 end 836 837 select @lob_compression = lobcomp_lvl 838 from master.dbo.sysdatabases 839 where name = @dbname 840 841 select @inrow_lob_length = inrowlen 842 from master.dbo.sysdatabases 843 where name = @dbname 844 end -- } 845 846 -- Generate the DROP syntax first. 847 -- 848 print "%1!PRINT ""<<<< %2!%3!%4!%5! %6!>>>>""%7!go%8!%9!" 849 , @newline 850 , @create_sql 851 , @imdb_ph 852 , @tempdb_ph 853 , @objtype 854 , @dbname 855 , @newline, @newline, @newline 856 857 print "IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases" 858 print " WHERE name = '%1!')", @dbname 859 print " DROP %1! %2!", @objtype, @dbname 860 print "go%1!%2!", @newline, @newline 861 862 print "IF (@@error != 0)%1!BEGIN", @newline 863 print " PRINT ""Error dropping %1! '%2!'""" 864 , @objtype_lower, @dbname 865 print " SELECT syb_quit()%1!END%2!go%3!%4!" 866 , @newline, @newline, @newline, @newline 867 868 -- Reset loop variables that are only to be used for 869 -- rows for one db. 870 -- 871 select @prev_segment = NULL 872 , @prev_crdate = NULL 873 874 -- We don't want to generate CREATE ARCHIVE DATABASE, 875 -- so reset the phrase, in a way to suppress that. 876 -- 877 if (@tempdb_ph = @archive_sql) 878 select @tempdb_ph = " " 879 880 -- Display special-case comments for archive db's DDL. 881 if (@archivedb = 1) 882 begin 883 print @archivedb_comm 884 end 885 886 print "%1!%2!%3!%4! %5!" 887 , @create_sql, @imdb_ph, @tempdb_ph 888 , @objtype, @dbname 889 890 891 892 893 -- Don't forget to add 'USE dbname AS TEMPLATE' clause 894 -- for the database if it is an in-memory db. 895 -- 896 if (@templatedbname IS NOT NULL) 897 begin 898 print "%1! USE %2! AS TEMPLATE" 899 , @tabchar 900 , @templatedbname 901 end 902 select @templatedbname = NULL 903 904 end -- } 905 906 907 908 if (@prev_segment IS NULL) 909 begin 910 select @prefix = @tabchar + " ON " 911 end 912 else if (@use_crdate = 1) and (@prev_crdate != @crdate) 913 begin -- { 914 -- State change in segment defn. Move to ALTER DB SQL 915 exec sp_ddlgen_db_print_alterdb 916 @dbname 917 , @archivedb 918 , @alterdb_sql 919 , @segment 920 , @durability 921 , @dmllogging 922 , @data_compression 923 , @lob_compression 924 , @inrow_lob_length 925 , @trace 926 , 'dates are different' 927 , @with_override output 928 , @with_dur_and_dmllog output 929 , @with_dcomp_and_IRL output 930 , @prefix output 931 end -- } 932 933 else if (@prev_segment = @segment) 934 begin 935 select @prefix = @tabchar + " , " 936 end 937 938 else if ((@prev_segment != "data and log") 939 and (@segment = "log only")) 940 begin 941 select @prefix = @tabchar + "LOG ON " 942 end 943 else 944 begin -- { 945 -- State change in segment defn. Move to ALTER DB SQL 946 exec sp_ddlgen_db_print_alterdb 947 @dbname 948 , @archivedb 949 , @alterdb_sql 950 , @segment 951 , @durability 952 , @dmllogging 953 , @data_compression 954 , @lob_compression 955 , @inrow_lob_length 956 , @trace 957 , 'segments are different' 958 , @with_override output 959 , @with_dur_and_dmllog output 960 , @with_dcomp_and_IRL output 961 , @prefix output 962 end -- } 963 964 -- Depending on whether the server supports unit-specifiers, 965 -- pick the way 'size' is printed. If the server supports 966 -- unit-specifiers, report using units, and report the # of 967 -- pages in comments. Otherwise, do the opposite. 968 -- 969 if (@units_ok = 1) 970 begin 971 select @size_str = "'" + @size_mb + "'" 972 , @size_comments = @comment 973 + convert(varchar, @size_pages) 974 + @pages 975 end 976 else 977 begin 978 select @size_str = convert(varchar, @size_pages) 979 , @size_comments = @comment + @size_mb 980 end 981 982 if (@trace = 1) 983 begin 984 select @size_comments = @size_comments 985 + " [" 986 + @segment 987 988 + ",lstart=" 989 + convert(varchar, @lstart) 990 991 + ",override=" 992 + convert(varchar, @override) 993 994 + "," 995 + convert(varchar, @crdate) 996 + "]" 997 end 998 999 -- In this cursor loop we could be generating the syntax 1000 -- for more than one disk piece, followed by more than 1001 -- one disk piece for the LOG ON phrase. Here, we remember 1002 -- if any one of these would have needed a WITH OVERRIDE 1003 -- clause, which should only be generated at the very end 1004 -- of the block of SQL for all the disk pieces being 1005 -- printed under one ALTER DATABASE statement. Then, we 1006 -- will pass this @with_override to the print routine which 1007 -- will terminate the current ALTER DATABASE SQL chunk with 1008 -- any required WITH OVERRIDE clause, and start a new block 1009 -- of SQL. 1010 1011 if (@override = 1) 1012 select @with_override = 1 1013 1014 print "%1!%2! = %3! %4!" 1015 , @prefix, @devname, @size_str 1016 , @size_comments 1017 1018 -- Store this disk piece's values, for comparison with those 1019 -- of the next piece. 1020 select @prev_segment = @segment 1021 , @prev_crdate = @crdate 1022 , @prev_dbname = @dbname 1023 1024 end -- } 1025 close diskcur 1026 deallocate cursor diskcur 1027 1028 if (@retval != 0) 1029 return @retval 1030 1031 -- Handle the case that the very last disk piece might need this clause. 1032 -- 1033 exec sp_ddlgen_db_print_with_clause @with_override output 1034 , @with_dur_and_dmllog output 1035 , @with_dcomp_and_IRL output 1036 , @durability 1037 , @dmllogging 1038 , @data_compression 1039 , @lob_compression 1040 , @inrow_lob_length 1041 , @archivedb 1042 1043 return @retval 1044 end -- } 1045
| RESULT SETS | |
| sp_ddlgen_database_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysdevices (1)  read_writes table tempdb..#seginfo (1) calls proc sybsystemprocs..sp_ddlgen_db_print_with_clause reads table master..sysusages (1) reads table master..sysdatabases (1) reads table master..spt_values (1) writes table tempdb..#failed_dbids (1) calls proc sybsystemprocs..sp_ddlgen_db_print_alterdb calls proc sybsystemprocs..sp_ddlgen_db_print_with_clause calls proc sybsystemprocs..sp_autoformat  reads table master..systypes (1) reads table tempdb..syscolumns (1)  read_writes table tempdb..#colinfo_af (1) calls proc sybsystemprocs..sp_autoformat reads table master..syscolumns (1) reads table tempdb..systypes (1) calls proc sybsystemprocs..sp_namecrack |
