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

RESULT SETS
sp_ddlgen_database_rset_001

DEFECTS
 QBGB 6 Bad group by : Query requires a 'group by' clause 408
 QJWI 5 Join or Sarg Without Index 413
 QJWI 5 Join or Sarg Without Index 445
 MEST 4 Empty String will be replaced by Single Space 709
 MTYP 4 Assignment type mismatch @templatedbname: varchar(30) = varchar(349) 813
 MTYP 4 Assignment type mismatch @templatedbname: varchar(30) = varchar(349) 827
 MTYP 4 Assignment type mismatch @dbname: varchar(30) = varchar(255) 969
 MTYP 4 Assignment type mismatch @dbname: varchar(30) = varchar(255) 1002
 MTYP 4 Assignment type mismatch @prev_dbname: varchar(30) = varchar(255) 1079
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 451
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
285
 TNOI 4 Table with no index tempdb..V1_D3 tempdb..V1_D3
 TNOI 4 Table with no index tempdb..V2_D4 tempdb..V2_D4
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% master..sysobjects.id: Warning message on sysobjects 883
 MAW1 3 Warning message on %name% master..sysobjects.id: Warning message on sysobjects 893
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public master..sysobjects  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public master..sysusers  
 MNAC 3 Not using ANSI 'is null' 882
 MNAC 3 Not using ANSI 'is null' 892
 MNEJ 3 'Not Equal' join 414
 MNER 3 No Error Check should check @@error after select into 354
 MNER 3 No Error Check should check @@error after select into 431
 MNER 3 No Error Check should check return value of exec 451
 MNER 3 No Error Check should check @@error after update 570
 MNER 3 No Error Check should check return value of exec 646
 MNER 3 No Error Check should check return value of exec 968
 MNER 3 No Error Check should check return value of exec 1001
 MNER 3 No Error Check should check return value of exec 1090
 MUCO 3 Useless Code Useless Brackets in create proc 28
 MUCO 3 Useless Code Useless Begin-End Pair 32
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 293
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 313
 MUCO 3 Useless Code Useless Brackets 317
 MUCO 3 Useless Code Useless Brackets 325
 MUCO 3 Useless Code Useless Brackets 335
 MUCO 3 Useless Code Useless Brackets 363
 MUCO 3 Useless Code Useless Brackets 364
 MUCO 3 Useless Code Useless Brackets 417
 MUCO 3 Useless Code Useless Brackets 429
 MUCO 3 Useless Code Useless Brackets 487
 MUCO 3 Useless Code Useless Brackets 489
 MUCO 3 Useless Code Useless Brackets 503
 MUCO 3 Useless Code Useless Brackets 519
 MUCO 3 Useless Code Useless Brackets 521
 MUCO 3 Useless Code Useless Brackets 603
 MUCO 3 Useless Code Useless Brackets 608
 MUCO 3 Useless Code Useless Brackets 611
 MUCO 3 Useless Code Useless Brackets 633
 MUCO 3 Useless Code Useless Brackets 640
 MUCO 3 Useless Code Useless Brackets 660
 MUCO 3 Useless Code Useless Brackets 671
 MUCO 3 Useless Code Useless Brackets 679
 MUCO 3 Useless Code Useless Brackets 703
 MUCO 3 Useless Code Useless Brackets 716
 MUCO 3 Useless Code Useless Brackets 718
 MUCO 3 Useless Code Useless Brackets 760
 MUCO 3 Useless Code Useless Brackets 779
 MUCO 3 Useless Code Useless Brackets 800
 MUCO 3 Useless Code Useless Brackets 811
 MUCO 3 Useless Code Useless Brackets 819
 MUCO 3 Useless Code Useless Brackets 822
 MUCO 3 Useless Code Useless Brackets 833
 MUCO 3 Useless Code Useless Brackets 841
 MUCO 3 Useless Code Useless Brackets 849
 MUCO 3 Useless Code Useless Brackets 873
 MUCO 3 Useless Code Useless Brackets 884
 MUCO 3 Useless Code Useless Brackets 930
 MUCO 3 Useless Code Useless Brackets 934
 MUCO 3 Useless Code Useless Brackets 949
 MUCO 3 Useless Code Useless Brackets 961
 MUCO 3 Useless Code Useless Brackets 988
 MUCO 3 Useless Code Useless Brackets 993
 MUCO 3 Useless Code Useless Brackets 1026
 MUCO 3 Useless Code Useless Brackets 1039
 MUCO 3 Useless Code Useless Brackets 1068
 MUCO 3 Useless Code Useless Brackets 1085
 QAFM 3 Var Assignment from potentially many rows 283
 QAFM 3 Var Assignment from potentially many rows 288
 QAFM 3 Var Assignment from potentially many rows 408
 QCRS 3 Conditional Result Set 495
 QCTC 3 Conditional Table Creation 431
 QJWT 3 Join or Sarg Without Index on temp table 575
 QJWT 3 Join or Sarg Without Index on temp table 585
 QNAJ 3 Not using ANSI Inner Join 384
 QNAJ 3 Not using ANSI Inner Join 409
 QNAJ 3 Not using ANSI Inner Join 441
 QNAJ 3 Not using ANSI Inner Join 496
 QNAJ 3 Not using ANSI Inner Join 879
 QNAJ 3 Not using ANSI Inner Join 888
 QPNC 3 No column in condition 387
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.ncsysusages unique
(vdevno, vstart)
Intersection: {vdevno}
412
 QTJ1 3 Table only appears in inner join clause 410
 QTJ1 3 Table only appears in inner join clause 412
 QTJ1 3 Table only appears in inner join clause 497
 VNRD 3 Variable is not read @override_sql 173
 VNRD 3 Variable is not read @durability_full_status 270
 VNRD 3 Variable is not read @ase_150x_or_gt 338
 VNRD 3 Variable is not read @with_override 1090
 VNRD 3 Variable is not read @with_dur_and_dmllog 1091
 VNRD 3 Variable is not read @with_dcomp_and_IRL 1092
 VNRD 3 Variable is not read @with_dek_name 1093
 VUNU 3 Variable is not used @instancename 81
 VUNU 3 Variable is not used @sqlstmt 136
 CRDO 2 Read Only Cursor Marker (has for read only clause) 592
 MDRV 2 Derived Table Marker 409
 MDRV 2 Derived Table Marker 411
 MDRV 2 Derived Table Marker 441
 MDRV 2 Derived Table Marker 443
 MRST 2 Result Set Marker 495
 MSUB 2 Subquery Marker 408
 MSUB 2 Subquery Marker 465
 MSUB 2 Subquery Marker 687
 MSUB 2 Subquery Marker 692
 MSUC 2 Correlated Subquery Marker 510
 MSUC 2 Correlated Subquery Marker 574
 MSUC 2 Correlated Subquery Marker 578
 MTR1 2 Metrics: Comments Ratio Comments: 36% 28
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 71 = 75dec - 6exi + 2 28
 MTR3 2 Metrics: Query Complexity Complexity: 299 28
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 354
 PRED_QUERY_COLLECTION 2 {v1d1=tempdb..V1_D1, v2d2=tempdb..V2_D2} 0 408
 PRED_QUERY_COLLECTION 2 {v1d3=tempdb..V1_D3, v2d4=tempdb..V2_D4} 0 431
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, o=master..sysobjects} 0 878
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, o=master..sysobjects, u=master..sysusers} 0 887

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#failed_dbids (1) 
reads table master..sysdatabases (1)  
reads table master..sysdevices (1)  
reads table master..sysusages (1)  
calls proc sybsystemprocs..sp_ddlgen_db_print_with_clause  
reads table master..sysobjects (1)  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
calls proc sybsystemprocs..sp_ddlgen_db_print_alterdb  
   calls proc sybsystemprocs..sp_ddlgen_db_print_with_clause  
read_writes table tempdb..#seginfo (1) 
reads table master..spt_values (1)  
reads table master..sysusers (1)  
writes table sybsystemprocs..sp_ddlgen_database_rset_001