DatabaseProcApplicationCreatedLinks
sybsystemprocssp_downgrade  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** SP_DOWNGRADE
4     ** 
5     ** Description
6     **	This stored procedure must be executed to downgrade a server
7     **	from version to a previous one.
8     ** 
9     ** Parameters
10    **	@cmd	- 'help', 'prepare' or 'downgrade'. If null, 'help'
11    **		  is the default.
12    **	@toversion	- Target version in char format
13    **	@verbose	- 0 or 1, to control verbosity
14    **	@override	- 0 or 1: if 1, skip databases that are not writable
15    **	@show_cmd	- if 1 and verbose=1, print command text before executing it
16    ** Returns
17    **	0 - success
18    **	1 - error
19    {
20    */
21    create or replace procedure sp_downgrade
22        @cmd varchar(10) = 'help',
23        @toversion varchar(10) = null,
24        @verbose int = 0,
25        @override int = 0,
26        @show_cmd int = 0
27    as
28        begin -- {
29            declare @dbid int,
30                @dbname sysname,
31                @orig_dbname sysname,
32                @devname sysname,
33                @cachename varchar(255),
34                @alldbs int,
35                @allbutmasterdb int,
36                @masterdb int,
37                @sybsecuritydb int,
38                @whatdbid int,
39                @fromversion varchar(10),
40                @fromversid int,
41                @toversid int,
42                @any15versid int,
43                @actionid int,
44                @baddbstat int,
45                @baddbstat2 int,
46                @baddbstat3 int,
47                @imdbstat int,
48                @damstat int,
49                @cachedevstat int,
50                @imcachestat int,
51                @auinitclass int,
52                @dbstat4 int,
53                @retval int,
54                @maxlen int,
55                @pgcount bigint,
56                @prepare_error_count int,
57                @exec_error_count int,
58                @lob_comp_error int,
59                @usage varchar(128),
60                @sqlbuf varchar(1024),
61                @sqlcmd varchar(2048),
62                @sqlclause varchar(1024),
63                @dbnameoffset int,
64                @downgrade_sysconfigures_15015_cmd varchar(1024),
65                @update_sysprocedures_sql varchar(128),
66                @drop_1502_sysoptions_sql varchar(512),
67                @dbinfo_reset_asevers_sql varchar(100),
68                @dbinfo_turnon_downgradeneed_sql varchar(512),
69                @dbinfo_update_1550_master_sql varchar(128),
70                @dbinfo_update_1550_userdb_sql varchar(128),
71                @dbinfo_update_1502_master_sql varchar(128),
72                @dbinfo_update_1502_userdb_sql varchar(128),
73                @dbinfo_update_15015_alldbs_sql varchar(128),
74                @dbinfo_update_1500_master_sql varchar(128),
75                @dbinfo_update_1500_userdb_sql varchar(128),
76                @errorlog_msg varchar(128),
77                @downgrade_passwords_sql varchar(128),
78                @downgrade_sysauditoptions_sql varchar(256),
79                @downgrade_dump_config varchar(40),
80                @downgrade_sysdams varchar(55),
81                @downgrade_sysconfigures_15015_sql1 varchar(512),
82                @downgrade_sysconfigures_15015_sql2 varchar(512),
83                @downgrade_sysconfigures_15015_sql3 varchar(512),
84                @downgrade_15015_config varchar(30),
85                @reset_sysservers_srvnetname_len varchar(128),
86                @downgrade_15015_msg varchar(30),
87                @sysobjstat3_sql varchar(20),
88                @durability_sql varchar(256),
89                @soid int,
90                @soname varchar(30),
91                @objname sysname,
92                @objstat3 smallint,
93                @csid int,
94                @wide_rows int,
95                @page_comp int,
96                @row_comp int,
97                @v char(1),
98                @c int,
99                @d int,
100               @cct int,
101               @lp_status int,
102               @exempt_lock int,
103               @lockrole int,
104               @dbsz unsigned int, -- total db size
105               @ppgb unsigned int, -- logical pages per Gb
106               @hi_db_size unsigned int,
107               @dummy int,
108               @nullarg char(1),
109               @gp_enabled int,
110               @status1 int,
111               @authmech int,
112               @identity_ins int,
113               @identity_upd int,
114               @tab_str char(3),
115               @blank_str char(3),
116               @new_datatypes varchar(256),
117               @downgrd_sysprot_index int,
118               @dealloc_ftp int
119   
120           select @status1 = 1
121           select @nullarg = NULL
122           select @identity_ins = 83
123           select @identity_upd = 84
124           select @authmech = 243
125           select @tab_str = '%	%'
126           select @blank_str = '% %'
127           select @downgrd_sysprot_index = 128
128   
129           set nocount on
130   
131           select @usage = 'sp_downgrade @cmd = {''prepare'' | ''downgrade'' | ''help''}, @toversion = ''n'' [, @verbose = 0 | 1 ] [, @override = 0 | 1]'
132   
133           select @cmd = lower(isnull(@cmd, 'downgrade'))
134   
135           if ((@toversion is null) or (@cmd = 'help'))
136           begin
137               print @usage
138               return 0
139           end
140   
141           if (@cmd not in ('prepare', 'downgrade'))
142           begin
143               print @usage
144               return 1
145           end
146   
147           /*
148           ** If granular permissions is not enabled, 
149           ** because this procedure could update security tables,
150           ** SSO role is required and because it is an administration
151           ** tool SA role is also required. The sybase_ts_role is
152           ** needed to execute the command dbcc markprocs.
153           ** 
154           */
155           execute @status1 = sp_aux_checkroleperm "sa_role",
156               "sybase_ts_role", @nullarg, @gp_enabled output
157   
158           if (@gp_enabled = 1)
159           begin
160               /* Disable granular permissions before running downgrade */
161               print 'Disable granular permissions before running downgrade'
162               return 1
163           end
164           else if (proc_role("sa_role") = 0 or proc_role("sso_role") = 0
165                   or proc_role("sybase_ts_role") = 0)
166           begin
167               return (1)
168           end
169   
170           if db_name() != "master"
171           begin
172               /* 17428, "You must be in the 'master' database in order to change database options." */
173               raiserror 17428
174               return 1
175           end
176   
177           /* Initialize */
178           select @prepare_error_count = 0,
179               @any15versid = 0,
180               @lp_status = 512,
181               @exempt_lock = 8,
182               @lockrole = 2
183   
184           /* Determine which version we are downgrading from */
185           select @fromversid = coalesce (nullif (convert(smallint,
186                   dbinfo_get('master', 'ASEvers')), 0),
187                   @@version_number)
188           select @fromversion = convert(varchar(2), @fromversid / 1000)
189               + '.' + convert(varchar(1), ((@fromversid / 100) % 10))
190               + '.' + convert(varchar(1), ((@fromversid / 10) % 10))
191               + '.' + convert(varchar(1), (@fromversid % 10))
192   
193           /*
194           ** Valid 'to' versions: any version whose major number equals
195           ** the @fromversid major number, and is < @fromversid. As a
196           ** convenience, if the lowest-order digit of the downgrade version
197           ** is not given, it defaults to [same as current server version].
198           ** This preserves the distinction between SMP and CE.
199           */
200           select @toversid = 0
201           select @cct = datalength(@toversion)
202           select @c = 1
203           while @c <= @cct
204           begin
205               select @v = substring(@toversion, @c, 1)
206               if (@v between '0' and '9')
207               begin
208                   select @toversid = (@toversid * 10) + convert(int, @v)
209               end
210               else if (@v != '.')
211               begin
212                   -- Not a valid version ... stop
213                   print "'%1!' is not a valid downgrade version.", @toversion
214                   return 1
215               end
216               select @c = @c + 1
217           end
218   
219           if (@verbose = 1)
220           begin
221               print "@fromversid = %1!' @fromversion = '%2!' @toversid = %3!",
222                   @fromversid, @fromversion, @toversid
223           end
224   
225           if (@toversid > 0)
226           begin
227               while (@toversid < 1000)
228                   select @toversid = @toversid * 10
229               if (@toversid < 10000)
230                   select @toversid = @toversid * 10
231                       + (@fromversid % 10)
232           end
233   
234           select @toversion = convert(varchar(2), @toversid / 1000)
235               + '.' + convert(varchar(1), ((@toversid / 100) % 10))
236               + '.' + convert(varchar(1), ((@toversid / 10) % 10))
237               + '.' + convert(varchar(1), (@toversid % 10))
238   
239           /* This is a broad test for @toversion correctness */
240           if (@toversid not in
241                   (15000, 15010, 15015, 15020, 15030, 15035, 15500, 15505,
242                       15700))
243           begin
244               print "'%1!' is not a valid downgrade version.", @toversion
245               return 1
246           end
247   
248           /*
249           ** Major-version downgrade is not allowed.
250           ** Cannot "downgrade" to a higher release than current.
251           ** Downgrade between SDC and SMP is disallowed; these are
252           ** denoted by SDC=5 in the subminor number, SMP=0.
253           ** 15035 server must only be downgraded to 15015, but
254           ** not existing 15005 nor 15025, there is no need
255           ** for an extra check.
256           */
257           if (((@toversid / 1000) != (@fromversid / 1000))
258                   or (@fromversid < @toversid)
259                   or ((@fromversid % 10) != (@toversid % 10)))
260           begin
261               print "'%1!' is not a valid downgrade version from %2!.",
262                   @toversion, @fromversion
263               return 1
264           end
265   
266           print "Downgrade from %1! to %2! (command: '%3!')", @fromversion,
267               @toversion, @cmd
268   
269           /*
270           ** Downgrade can only be performed when server is under single
271           ** user mode.
272           */
273           if (is_singleusermode() = 0)
274           begin
275               if (@cmd = 'prepare')
276               begin
277                   print 'Warning: Server is not in single user mode - may find spurious problems due to transactions in progress.'
278                   print 'Try restarting the server using the -m option before downgrade.'
279               end
280               else if (@cmd = 'downgrade')
281               begin
282                   print 'You cannot run sp_downgrade; server must be in single-user mode.'
283                   print 'Try restarting the server using the -m option.'
284                   return 1
285               end
286           end
287   
288           /* 
289           ** Downgrade only those databases that are writable 
290           **
291           ** sysdatabases.status not in 0x20 (in load), 0x40 (not rec), 
292           ** 0x80 (bypass), 0x100 (suspect), 0x400 (rdonly), 0x8000 (emergency)
293           **
294           ** sysdatabases.status2 not in 0x10 (offline), 0x400 (online for 
295           ** standby access)
296           **
297           ** sysdatabases.status3 not in 0x1 (user proxy), 0x2 (ha proxy),
298           ** 0x8 (shutdown), 0x10 (failedover), 0x80 (quiesced), 0x400000 
299           ** (archive)
300           */
301           select @baddbstat = 34272, @baddbstat2 = 1040,
302               @baddbstat3 = 4194459
303   
304           select @imdbstat = number from master..spt_values
305           where name = "in-memory database"
306   
307           select dbid
308           into #sysdatabases
309           from master.dbo.sysdatabases
310           where status & @baddbstat = 0
311               and status2 & @baddbstat2 = 0
312               and status3 & @baddbstat3 = 0
313               and status4 & @imdbstat = 0
314           order by dbid
315   
316           select a.dbid, a.name
317           into #baddb
318           from master.dbo.sysdatabases a
319           where not exists (select 1 from #sysdatabases b
320                   where a.dbid = b.dbid)
321   
322           if (@@rowcount > 0)
323           begin
324               /*
325               ** If there is any database that we know in advance that cannot
326               ** be downgraded, skip it only if WITH OVERRIDE was specified.
327               */
328               if (@override = 0)
329               begin
330                   print 'sp_downgrade cannot be run because the following databases are not writable or they cannot be downgraded.'
331                   print 'Use sp_downgrade ... @override = 1 to skip these databases.'
332               end
333               else
334               begin
335                   print 'sp_downgrade will skip the following databases because they are not writable or cannot be downgraded.'
336               end
337   
338               select dbid, convert(char(30), name) as name
339               from #baddb
340   
341               if (@override = 0)
342               begin
343                   return 1
344               end
345           end
346   
347           -- If we are at the requested downgrade level, check whether all
348           -- databases have been downgraded.  If so, stop with success;
349           -- else declare an error.
350           if (@fromversid = @toversid)
351           begin
352               print "This installation is already at version %1!", @toversion
353               select @dbid = 2
354               while @dbid is not null
355               begin
356                   if (not exists (select dbid from #baddb where dbid = @dbid))
357                       and (@toversid != (select convert(smallint,
358                               dbinfo_get(@dbid, 'asevers'))))
359                   begin
360                       print "Database ID %1! is not at the correct version.",
361                           @dbid
362                       select @prepare_error_count = @prepare_error_count + 1
363                   end
364                   select @dbid = min(dbid)
365                   from master.dbo.sysdatabases
366                   where dbid > @dbid
367               end
368               if @prepare_error_count != 0
369               begin
370                   print "sp_downgrade cannot continue. Run 'online database' for all databases to bring them back up to ASE's current release level, then retry sp_downgrade."
371                   return 1
372               end
373               return 0
374           end
375   
376           select @errorlog_msg = 'Preparing ASE downgrade from ' + @fromversion
377               + ' to ' + @toversion + '.'
378           dbcc printolog(@errorlog_msg)
379   
380           /*
381           ** We will verify encryption downgrade and prepare for
382           ** password downgrade for both prepare mode and downgrade mode.
383           ** If either fail, we return fail.
384           ** Initally set retval to 0. It will be set by sp_encryption if 
385           ** sp_encryption fails or by sp_passwordpolicy if sp_passwordpolicy
386           ** fails.
387           */
388           select @retval = 0
389   
390           /* 
391           ** verify_downgrade, 15.5, 15.5SDC, 15.0.3, 15.0.2 are at the 
392           ** same level of the encrypted column feature, 15.0.1 and 15.0.1SDC
393           ** are at the same level of the encrypted column feature
394           */
395           if (not exists (select name from syscolumns
396                       where id = object_id('syscomments') and name = 'encrkeyid'))
397           begin
398               -- This is a re-run of sp_downgrade; skip sp_encryption
399               print "This is a re-run of sp_downgrade; skip sp_encryption"
400               select @retval = 0
401           end
402           else if (@toversid >= 15020)
403           begin
404               exec @retval = sp_encryption 'verify_downgrade', '15.5'
405           end
406           else if (@toversid = 15010 or @toversid = 15015)
407           begin
408               exec @retval = sp_encryption 'verify_downgrade', '15.0.1'
409           end
410           else if (@toversid = 15000)
411           begin
412               exec @retval = sp_encryption 'verify_downgrade', '15.0'
413           end
414   
415           if @retval != 0
416           begin
417               select @prepare_error_count = @prepare_error_count + 1
418           end
419   
420           /*
421           ** Starting with 15.0.2, new password encryption algorithm is used to
422           ** encrypt login passwords.
423           ** On downgrade to SMP 15.0.1 or prior and SDC version 15.0.1.5,
424           ** the login password is reset for logins with password encrypted 
425           ** only using the new algorithm.
426           */
427           if (@toversid <= 15015)
428           begin
429               exec @retval = sp_passwordpolicy 'prepare_downgrade'
430   
431               if @retval != 0
432               begin
433                   select @prepare_error_count = @prepare_error_count + 1
434               end
435           end
436   
437           if (@toversid < 15500)
438           begin
439               -- Some datatypes don't exist before 15.5.  Assure they are unused.
440               print "Checking datatypes in preparation for downgrade"
441   
442               select @retval = 0
443               select @new_datatypes = '(''bigdatetime'', ''bigtime'', ''bigdatetimen'', ''bigtimen'')'
444   
445               create table #objects(Name varchar(255), Owner varchar(255),
446                   Type char(2), DBname varchar(255))
447   
448               select @sqlbuf = 'insert #objects select distinct o.name, user_name(o.uid), o.type, db_name(@dbid) ' +
449                   'from :DBNAME:..syscolumns c, :DBNAME:..sysobjects o ' +
450                   'where c.type in (select type from :DBNAME:..systypes ' +
451                   'where name in ' + @new_datatypes + ') and c.id = o.id'
452   
453               select @dbid = db_id('master')
454               while (@dbid is not null)
455               begin
456                   select @dbname = name
457                   from master..sysdatabases
458                   where dbid = @dbid
459   
460                   select @orig_dbname = @dbname
461                   /*
462                   ** Database name in which there is tab or blank must
463                   ** be delimited with '[]' in sql cmd.
464                   */
465                   if ((patindex(@tab_str, @dbname) > 0)
466                           or (patindex(@blank_str, @dbname) > 0))
467                   begin
468                       select @dbname = '[' + @orig_dbname + ']'
469                   end
470   
471                   select @sqlcmd = @sqlbuf
472                   select @dbnameoffset = patindex('%:DBNAME:%', @sqlcmd)
473                   while (@dbnameoffset > 0)
474                   begin
475                       select @sqlcmd = stuff(@sqlcmd,
476                               @dbnameoffset, 8,
477                               @dbname)
478                       select @dbnameoffset = patindex('%:DBNAME:%',
479                               @sqlcmd)
480                   end
481   
482                   exec (@sqlcmd)
483   
484                   select @c = count(1) from #objects where DBname = @orig_dbname
485                   if (@c > 0)
486                   begin
487                       raiserror 19985, @toversion
488                       select @retval = 1
489                   end
490                   else
491                   if @verbose = 1
492                   begin
493                       print 'Database %1! does not use new datatypes',
494                           @orig_dbname
495                   end
496   
497                   select @dbid = min(dbid)
498                   from master..sysdatabases a
499                   where a.dbid > @dbid
500                       and not exists (select 1 from #baddb b
501                           where a.dbid = b.dbid)
502               end
503   
504               /* Block downgrade unless users remove usages of new datatypes */
505               if @retval != 0
506               begin
507                   select @prepare_error_count = @prepare_error_count + 1
508                   if @verbose = 1
509                   begin
510                       print 'ERROR: Some objects are found using new datatypes %1!. Recreate them without using new datatypes before downgrade',
511                           @new_datatypes
512                       print 'List of objects using new datatypes'
513                       exec sp_autoformat '#objects',
514                           'Name, Owner, Type, DBname'
515                   end
516                   print ''
517               end
518   
519               drop table #objects
520   
521               /*
522               ** Before 15.5 stored procedures cannot be created with
523               ** deferred name resolution. If we have procedures created
524               ** with deferred name resolution the downgrade machinery
525               ** will mark them to be recreated from text so they can
526               ** be executed in the downgraded server. But the text in
527               ** syscomments will have 'select *' not expanded, for
528               ** getting the 'select *' correctly expanded the procedures
529               ** would have to be recreated from SQL script.
530               **
531               ** So during 'prepare' we warn if there are databases having
532               ** stored procedures created with deferred name resolution
533               ** and in 'verbose' mode we print out the names for the
534               ** procedures in each database.
535               */
536               print "Checking procedures in preparation for downgrade"
537   
538               /* Cursor for traversing databases */
539               declare sysdbc2 cursor for
540               select dbid from #sysdatabases
541   
542               /* Initializations */
543               set @retval = 0
544               set @c = 0
545   
546               select Name = name, Owner = user_name(uid)
547               into #sprocDNR
548               from sysobjects
549               where 1 = 2
550   
551               select @sqlbuf = " truncate table #sprocDNR
552   				   insert #sprocDNR
553   				   select Name=o.name,
554   					  Owner=user_name(o.uid)
555   				   from :DBNAME:..sysobjects o
556   				   where exists (select 1 from
557   						 :DBNAME:..sysprocedures p
558   						 where p.id = o.id and
559   							p.type = 2048)
560   				   order by o.name"
561   
562               /* Scan sysprocedures in each database */
563               open sysdbc2
564               fetch sysdbc2 into @dbid
565   
566               while (@@sqlstatus = 0)
567               begin -- {
568   
569                   select @dbname = db_name(@dbid)
570   
571                   select @orig_dbname = @dbname
572                   /*
573                   ** Database name in which there is tab or blank must
574                   ** be delimited with '[]' in sql cmd.
575                   */
576                   if ((patindex(@tab_str, @dbname) > 0)
577                           or (patindex(@blank_str, @dbname) > 0))
578                   begin
579                       select @dbname = '[' + @orig_dbname + ']'
580                   end
581   
582                   select @sqlcmd = "select @c = count(1) from "
583                       + @dbname + "..sysprocedures " +
584                       "where type = 2048"
585   
586                   exec (@sqlcmd)
587   
588                   if (@c != 0)
589                   begin
590                       set @retval = 1
591                       print "Warning: There are procedures in database: %1! (dbid: %2!) created with deferred name resolution", @orig_dbname, @dbid
592   
593                       if (@verbose = 1)
594                       begin
595                           print "List of procedures created with deferred name resolution in database: %1! (dbid: %2!)", @orig_dbname, @dbid
596   
597                           select @sqlcmd = @sqlbuf
598                           select @dbnameoffset =
599                               patindex('%:DBNAME:%', @sqlcmd)
600   
601                           while (@dbnameoffset > 0)
602                           begin
603                               select @sqlcmd =
604                                   stuff(@sqlcmd,
605                                       @dbnameoffset,
606                                       8, @dbname)
607   
608                               select @dbnameoffset =
609                                   patindex('%:DBNAME:%',
610                                       @sqlcmd)
611                           end
612   
613                           exec (@sqlcmd)
614   
615                           exec sp_autoformat #sprocDNR
616   
617                           print ""
618                       end
619                   end
620   
621                   fetch sysdbc2 into @dbid
622   
623               end -- }
624   
625               if (@retval = 1)
626               begin
627                   /*
628                   ** At least one database has procedures created with
629                   ** deferred name resolution. Print a warning with
630                   ** the advice for recreating manually those procedures
631                   ** for getting the right "select *" expanded text in
632                   ** syscomments.
633                   */
634                   print ""
635                   print "Warning: Procedures created with deferred name resolution that contain statements with 'select *' syntax would need to be recreated manually from text. If they are not recreated they will still execute but the text in syscomments will remain with 'select *' unexpanded."
636                   print ""
637               end
638               else if (@verbose = 1)
639               begin
640                   /*
641                   ** We didn't find any procedure created with deferred
642                   ** name resolution.
643                   */
644                   print "There are no procedures created with deferred name resolution in any database"
645               end
646   
647               close sysdbc2
648               deallocate cursor sysdbc2
649           end
650   
651           if (@toversid < 15030 and @toversid != 15015)
652           begin -- {
653               /*
654               ** Starting at 15.0.3, the size of sysservers.srvnetname
655               ** changed from 32 bytes to 255 bytes.  If any srvnetname
656               ** is greater than 32 bytes do not let downgrade happen.
657               ** (exception: 15015 was original version for ssl=CN changes
658               ** and increase of srvnetname column.)
659               */
660               print "Checking sysservers in preparation for downgrade"
661               if exists (select 1 from master.dbo.sysservers
662                       where
663                           lower(srvnetname) like "%ssl=%"
664                           or datalength(srvnetname) > 32)
665               begin
666                   raiserror 19891, @fromversion, @toversion
667                   select srvname, srvnetname into #newformat
668                   from master.dbo.sysservers
669                   where
670                       lower(srvnetname) like "%ssl=%"
671                       or datalength(srvnetname) > 32
672   
673                   exec sp_autoformat
674                       @fulltabname = "#newformat",
675                       @selectlist = "'Server Name' = srvname,
676   				'Server Net Name' = srvnetname"
677   
678                   select @prepare_error_count = @prepare_error_count + 1
679               end
680               else
681               begin
682                   print "There were no errors which involve the sysservers table."
683               end
684   
685               /*
686               ** Starting at 15.0.3, the directory service entry may contain
687               ** CN=. If any row in syslisteners has such a
688               ** string, ssl was configured for this listener.  State that
689               ** ssl needs to be disabled and list the listeners.
690               */
691               print "Checking syslisteners in preparation for downgrade"
692               if exists (select 1 from master.dbo.syslisteners
693                       where
694                           lower(net_type) like "%ssl%"
695                           or lower(address_info) like "%cn=%")
696               begin
697                   raiserror 19943, @fromversion, @toversion
698                   select net_type, address_info into #newformat1
699                   from master.dbo.syslisteners
700                   where
701                       lower(net_type) like "%ssl%"
702                       or lower(address_info) like "%cn=%"
703   
704                   exec sp_autoformat
705                       @fulltabname = "#newformat1",
706                       @selectlist = "'Net Type' = net_type, 
707   				'Address Info' = address_info"
708   
709                   select @prepare_error_count = @prepare_error_count + 1
710               end
711               else
712               begin
713                   print "There were no errors which involve the syslisteners table."
714               end
715   
716               /*
717               ** Starting at 15.0.3, sysprocedures has additional 
718               ** qp_setting column that is used to associate optimization 
719               ** goal and criteria with stored procedures. If the 
720               ** qp_setting column is not null, print an warning to user. 
721               */
722               print "Checking sysprocedures in preparation for downgrade"
723   
724               /* A cursor to traverse sysdatabases */
725               declare sysdbc1 cursor for
726               select dbid from #sysdatabases
727   
728               set @retval = 0
729               open sysdbc1
730               fetch sysdbc1 into @dbid
731               while (@@sqlstatus = 0)
732               begin -- {
733                   select @dbname = db_name(@dbid)
734   
735                   select @orig_dbname = @dbname
736                   /*
737                   ** Database name in which there is tab or blank must
738                   ** be delimited with '[]' in sql cmd.
739                   */
740                   if ((patindex(@tab_str, @dbname) > 0)
741                           or (patindex(@blank_str, @dbname) > 0))
742                   begin
743                       select @dbname = '[' + @orig_dbname + ']'
744                   end
745   
746                   select @sqlcmd = 'select @retval = count(1) from '
747                       + @dbname + '..sysprocedures ' +
748                       'where qp_setting is not null'
749                   exec (@sqlcmd)
750                   if @retval != 0
751                   begin
752                       print "Warning: There are non-NULL qp_setting rows in sysprocedures of database: %1! (dbid: %2!).", @orig_dbname, @dbid
753                       print "The qp_setting column in sysprocedures will be set to NULL and dropped during downgrade."
754                   end
755                   fetch sysdbc1 into @dbid
756               end --}
757               close sysdbc1
758               deallocate cursor sysdbc1
759   
760   
761               /*
762               ** Starting at 15.0.3, sysqueryplans has additional columns:
763               ** dbid, qpdate, sprocid, hashkey2, key1, key2, key3, key4.
764               ** If anyone of them is not null, print a warning message.
765               */
766               print "Preparing sysqueryplans for downgrade"
767   
768               /* A cursor to traverse sysdatabases */
769               declare sysdbc1 cursor for
770               select dbid from #sysdatabases
771   
772               set @retval = 0
773               open sysdbc1
774               fetch sysdbc1 into @dbid
775               while (@@sqlstatus = 0)
776               begin -- {
777                   select @dbname = db_name(@dbid)
778   
779                   select @orig_dbname = @dbname
780                   /*
781                   ** Database name in which there is tab or blank
782                   ** must be delimited with '[]' in sql cmd.
783                   */
784                   if ((patindex(@tab_str, @dbname) > 0)
785                           or (patindex(@blank_str, @dbname) > 0))
786                   begin
787                       select @dbname = '[' + @orig_dbname + ']'
788                   end
789   
790                   select @sqlcmd = 'select @retval = count(1) from '
791                       + @dbname + '..sysqueryplans ' +
792                       'where dbid is not null ' +
793                       'or qpdate is not null ' +
794                       'or sprocid is not null ' +
795                       'or hashkey2 is not null ' +
796                       'or key1 is not null ' +
797                       'or key2 is not null ' +
798                       'or key3 is not null ' +
799                       'or key4 is not null'
800                   exec (@sqlcmd)
801                   if @retval != 0
802                   begin
803                       print "Warning: There are non-NULL rows for dbid or qpdate or sprocid or hashkey2 or key1 or key2 or key3 or key4 in sysqueryplans of database: %1! (dbid: %2!).", @orig_dbname, @dbid
804                       print "The respective column in sysqueryplans will be set to NULL and dropped during downgrade."
805                   end
806                   fetch sysdbc1 into @dbid
807               end --}
808               close sysdbc1
809               deallocate cursor sysdbc1
810   
811               /*
812               ** In 15.0.3 ESD#1, a new charset KZ1048 is added. Do not allow
813               ** downgrade to happen if server is using this charset.
814               */
815               select @csid = value
816               from master.dbo.syscurconfigs
817               where config = 131
818               if (@csid = 87)
819               begin
820                   print ""
821                   raiserror 19954, @toversion, 'kz1048'
822                   select @prepare_error_count = @prepare_error_count + 1
823               end
824   
825           end --}
826   
827           /*
828           ** In 15.0.2, new nocase sortorders (id 52) are added for
829           ** Chinese and Japanese character sets: eucgb, gb18030, cp936,
830           ** eucjis, sjis and deckanji. If server is using these sortorder
831           ** as default, do not allow downgrade to happen.
832           */
833           if (@toversid < 15020)
834           begin -- {
835               select @soid = value
836               from master.dbo.syscurconfigs
837               where config = 123
838   
839               select @csid = value
840               from master.dbo.syscurconfigs
841               where config = 131
842   
843               select @soname = name
844               from master.dbo.syscharsets
845               where id = @soid and csid = @csid and type between 2000 and 2999
846   
847               if (@soid = 52) and (@csid in (170, 171, 173, 140, 141, 142))
848               begin
849                   print ""
850                   raiserror 19779, @toversion, @soname
851                   select @prepare_error_count = @prepare_error_count + 1
852               end
853           end -- }
854   
855           /*
856           ** In 15.7.1, the table SYSDAMS was added to support cumulative dumps and
857           ** it has to be dropped. Disallow it if any database is configured to support
858           ** them.
859           */
860           if (@toversid < 15700)
861           begin
862               exec @retval = sp_downgrade_sysdams null, 'prepare', @show_cmd
863               if @retval != 0
864               begin
865                   select @prepare_error_count = @prepare_error_count + 1
866               end
867           end
868   
869           /*
870           ** In 15.5, status4 column of sysdatabases has new values indicating
871           ** MINIMAL logging mode and in-memory database. Downgrade will report
872           ** those databases which are using these values.
873           */
874           if (@toversid < 15500)
875           begin
876               print "Checking sysdatabases in preparation for downgrade"
877   
878               set @retval = 0
879   
880               select @imdbstat = 4096
881   
882               select @retval = count(1)
883               from master..sysdatabases
884               where (status4 & (@imdbstat | 256)) != 0
885   
886               if @retval != 0
887               begin
888                   declare sysdbc0 cursor for
889                   select dbid, status4 from master..sysdatabases
890                   where (status4 & (@imdbstat | 256)) != 0
891   
892                   open sysdbc0
893                   fetch sysdbc0 into @dbid, @dbstat4
894   
895                   while (@@sqlstatus = 0)
896                   begin
897                       select @dbname = db_name(@dbid)
898                       if (@dbstat4 & 256 = 256)
899                       begin
900                           print "Error: Database '%1!' with MINIMAL logging mode should be altered back to FULL logging mode before downgrade.", @dbname
901                       end
902                       if (@dbstat4 & @imdbstat = @imdbstat)
903                       begin
904                           print "Error: In-memory database '%1!' should be dropped before downgrade.", @dbname
905                       end
906                       fetch sysdbc0 into @dbid, @dbstat4
907                   end
908   
909                   close sysdbc0
910                   deallocate cursor sysdbc0
911   
912                   select @prepare_error_count = @prepare_error_count + 1
913               end
914   
915               /*
916               ** In 15.5, a new kind of device "cache device" is introduced.
917               ** Downgrade will advise users to drop these cache devices.
918               */
919               print "Checking sysdevices in preparation for downgrade"
920   
921               set @retval = 0
922   
923               select @cachedevstat = 8
924   
925               select @retval = count(1)
926               from master..sysdevices
927               where status2 & @cachedevstat = @cachedevstat
928   
929               if @retval != 0
930               begin
931                   declare sysdevc cursor for
932                   select name from master..sysdevices
933                   where status2 & @cachedevstat = @cachedevstat
934   
935                   open sysdevc
936                   fetch sysdevc into @devname
937   
938                   while (@@sqlstatus = 0)
939                   begin
940                       print "Error: Device '%1!' is a cache device, which should be dropped via sp_dropdevice before downgrade.", @devname
941                       fetch sysdevc into @devname
942                   end
943   
944                   close sysdevc
945                   deallocate cursor sysdevc
946   
947                   select @prepare_error_count = @prepare_error_count + 1
948               end
949   
950               /*
951               ** In 15.5, a new cache type "inmemory_storage" is introduced.
952               ** Downgrade will advise users to drop the user-defined caches
953               ** whose type is "inmemory_storage".
954               */
955               print "Checking user-defined caches in preparation for downgrade"
956   
957               set @retval = 0
958   
959               select @imcachestat = 65536
960   
961               select @retval = count(1)
962               from master..sysconfigures
963               where (parent = 19) and (status & @imcachestat = @imcachestat)
964   
965               if @retval != 0
966               begin
967                   declare usercachec cursor for
968                   select name from master..sysconfigures
969                   where (parent = 19) and (status & @imcachestat = @imcachestat)
970   
971                   open usercachec
972                   fetch usercachec into @cachename
973   
974                   while (@@sqlstatus = 0)
975                   begin
976                       print "Error: Cache '%1!' is a inmemory_storage cache, which should be dropped via sp_cacheconfig before downgrade.", @cachename
977                       fetch usercachec into @cachename
978                   end
979   
980                   close usercachec
981                   deallocate cursor usercachec
982   
983                   select @prepare_error_count = @prepare_error_count + 1
984               end
985   
986               /*
987               ** In 15.5, sysstat3 column of sysobjects is introduced.
988               ** Downgrade will clear the values of the column, and remove
989               ** it from syscolumns.
990               */
991               set @retval = 0
992               exec @retval = sp_downgrade_sysobjects "display"
993               if @retval != 0
994               begin
995                   select @prepare_error_count = @prepare_error_count + 1
996               end
997           end
998   
999           -- Look for databases having "allow wide dol rows" enabled
1000          if (@toversid < 15700 and @@maxpagesize > 8192)
1001          begin
1002              select @wide_rows = number
1003              from master.dbo.spt_values
1004              where type = "D4" and name = "allow wide dol rows"
1005              if (@wide_rows is null)
1006              begin
1007                  print 'Information is missing from spt_values.'
1008                  print 'Please re-run installmaster before continuing.'
1009                  select @prepare_error_count = @prepare_error_count + 1
1010              end
1011              else
1012              begin
1013                  -- Turn this off arbitrarily in temp databases.
1014                  -- Besides tempdb, there are some other types of temp dbs:
1015                  -- 0x100(for SDC, it means local user temp db, for SMP,
1016                  -- it means user created temp db, 0x20000000(local system
1017                  -- temp db), 0x40000000(global temp db).
1018                  update master.dbo.sysdatabases
1019                  set status4 = status4 & ~ @wide_rows
1020                  where name = 'tempdb'
1021                      or status3 & 256 = 256
1022                      or status3 & 536870912 = 536870912
1023                      or status3 & 1073741824 = 1073741824
1024  
1025                  -- Now look for other databases having it on
1026                  if exists (select name
1027                          from master.dbo.sysdatabases
1028                          where status4 & @wide_rows = @wide_rows)
1029                  begin
1030                      print "One or more databases still permit wide DOL rows."
1031                      select name as 'Investigate these databases:'
1032                      from master.dbo.sysdatabases
1033                      where status4 & @wide_rows = @wide_rows
1034                      select @prepare_error_count = @prepare_error_count + 1
1035                  end
1036                  else if (@verbose = 1)
1037                  begin
1038                      print "No databases allow wide DOL rows."
1039                  end
1040              end
1041          end
1042  
1043          -- Look for databases having compression enabled
1044          -- Look for databases having LOB compression enabled
1045          -- Look for databases using in-row as default storage for LOBs
1046          -- Look for databases having deallocate first text page enabled
1047          if (@toversid < 15700)
1048          begin
1049              select @page_comp = number
1050              from master.dbo.spt_values
1051              where type = 'D4' and name = 'page compressed'
1052  
1053              select @row_comp = number
1054              from master.dbo.spt_values
1055              where type = 'D4' and name = 'row compressed'
1056  
1057              if (@page_comp is null or @row_comp is null)
1058              begin
1059                  print 'Information is missing from spt_values.'
1060                  print 'Please re-run installmaster before continuing.'
1061                  select @prepare_error_count = @prepare_error_count + 1
1062              end
1063              else
1064              begin
1065                  -- look for databases configured for compression
1066                  if exists (select name
1067                          from master.dbo.sysdatabases
1068                          where status4 & (@page_comp | @row_comp) != 0)
1069                  begin
1070                      print "One or more databases is configured for compression."
1071                      select name as 'Investigate these databases:'
1072                      from master.dbo.sysdatabases
1073                      where status4 & (@page_comp | @row_comp) != 0
1074                      select @prepare_error_count = @prepare_error_count + 1
1075                  end
1076                  else if (@verbose = 1)
1077                  begin
1078                      print "No databases are configured for compression."
1079                  end
1080              end
1081  
1082              if exists (select 1
1083                      from master.dbo.sysdatabases
1084                      where (lobcomp_lvl is not null)
1085                          and (lobcomp_lvl > 0))
1086              begin
1087                  print '*** Some databases are configured for LOB compression.'
1088                  select distinct name as 'check these databases'
1089                  from master.dbo.sysdatabases where lobcomp_lvl > 0
1090                  select @prepare_error_count = @prepare_error_count + 1
1091              end
1092              else if (@verbose = 1)
1093              begin
1094                  print 'No databases are configured for LOB compression.'
1095              end
1096  
1097              if exists (select 1
1098                      from master.dbo.sysdatabases
1099                      where (inrowlen is not null)
1100                          and (inrowlen > 0))
1101              begin
1102                  print '*** Some databases are configured to use in-row LOBs.'
1103                  select distinct name as 'check these databases'
1104                  from master.dbo.sysdatabases where inrowlen > 0
1105                  select @prepare_error_count = @prepare_error_count + 1
1106              end
1107              else if (@verbose = 1)
1108              begin
1109                  print 'No databases are configured to use in-row LOBs.'
1110              end
1111  
1112              select @dealloc_ftp = number
1113              from master.dbo.spt_values
1114              where type = 'D4' and name = 'deallocate first text page'
1115              if (@dealloc_ftp is null)
1116              begin
1117                  print 'Information is missing from spt_values.'
1118                  print 'Please re-run installmaster before continuing.'
1119                  select @prepare_error_count = @prepare_error_count + 1
1120              end
1121              else
1122              begin
1123                  -- look for databases configured for 'deallocate first text page'
1124                  if exists (select name
1125                          from master.dbo.sysdatabases
1126                          where status4 & @dealloc_ftp != 0)
1127                  begin
1128                      print "*** Some databases have option 'deallocate first text page' turned on, run sp_dboption to turn off this option and retry."
1129                      select name as 'check these databases'
1130                      from master.dbo.sysdatabases
1131                      where status4 & @dealloc_ftp != 0
1132                      select @prepare_error_count = @prepare_error_count + 1
1133                  end
1134                  else if (@verbose = 1)
1135                  begin
1136                      print "No databases are configured to deallocate first text page after NULL update."
1137                  end
1138              end
1139          end
1140  
1141          /*
1142          ** If downgrading to a version prior to 15.7, check:
1143          ** - There are no log records of the type PAGEIMAGE or PAGE_COMPRESS
1144          ** - There are no databases that are being initialized by the
1145          **   Allocation Unit Initializer.
1146          ** - There are no tables having compression enabled
1147          ** - There are no tables having in-row LOBs
1148          ** - There are no replication filters.
1149          ** - There are no precomputed result sets
1150          ** - There are no tables in no-datacopy-drop-column state
1151          ** - There are no granular permissions, master specific DBCC permissions
1152          **   or select permission on builtin authmech granted to 
1153          **   UDRs (not sa_serverprivs_role), users or groups. These will be 
1154          **   deleted during downgrade.
1155          ** - There are no tables having attribute 'dealloc_first_txtpg' of value 2,
1156          **   which means don't deallocate first text page after NULL update.
1157          ** - There are no indexes using RID value comparison.
1158          **   
1159          */
1160          if (@toversid < 15700)
1161          begin --{
1162              select @retval = 0
1163  
1164              select @auinitclass = 42, @lob_comp_error = 0
1165  
1166              /*
1167              ** Create temporary table to store grantee info
1168              ** who are system roles, sa_serverprivs_role or dbo.
1169              */
1170              create table #grantee(uid int, name varchar(255))
1171  
1172              /* 
1173              ** Create temporary table to store user info for 
1174              ** printing 
1175              */
1176              create table #users(uid int, name varchar(255))
1177  
1178              /* 
1179              ** Create temporary table to store table info for 
1180              ** printing 
1181              */
1182              create table #tables(id int, objname varchar(255))
1183  
1184              /*
1185              ** Create temporary table to store permission info for
1186              ** grantees who are not system roles, UDR sa_serverprivs_role 
1187              ** or dbo.
1188              */
1189              create table #perm(id int, grantee int,
1190                  grantee_name varchar(255), action smallint,
1191                  type tinyint, grantor int)
1192  
1193              /* Only check the writeable and downgradable databases. */
1194              declare sysdbc0 cursor for
1195              select dbid, name from master.dbo.sysdatabases a
1196              where not exists (select 1 from #baddb b
1197                      where a.dbid = b.dbid)
1198  
1199              open sysdbc0
1200              fetch sysdbc0 into @dbid, @dbname
1201  
1202              while (@@sqlstatus = 0)
1203              begin --{
1204                  if (@verbose = 1)
1205                  begin
1206                      print 'Checking database ''%1!'' for downgrade readiness.', @dbname
1207                  end
1208  
1209                  -- Prestage checkpoint to clear the log of any offending
1210                  -- log streams that will prevent downgrade.
1211                  --
1212                  exec sp_getmessage 19112, @sqlbuf output
1213                  print @sqlbuf, @dbname, @cmd
1214  
1215                  checkpoint @dbname
1216                  if (@@error != 0)
1217                  begin
1218                      exec sp_getmessage 19113, @sqlbuf output
1219                      print @sqlbuf, @dbname
1220  
1221                      select @prepare_error_count = @prepare_error_count + 1
1222                  end
1223  
1224  
1225                  if exists (select 1 from master.dbo.sysattributes
1226                          where class = @auinitclass and object = @dbid)
1227                  begin --{
1228                      print "Error: Database '%1!' is not fully initialized. You must wait for the Allocation Unit Initializer to complete.", @dbname
1229                      select @prepare_error_count = @prepare_error_count + 1
1230                  end --}
1231  
1232                  select @orig_dbname = @dbname
1233                  /*
1234                  ** Database name in which there is tab or blank must
1235                  ** be delimited with '[]' in sql cmd.
1236                  */
1237                  if ((patindex(@tab_str, @dbname) > 0)
1238                          or (patindex(@blank_str, @dbname) > 0))
1239                  begin
1240                      select @dbname = '[' + @orig_dbname + ']'
1241                  end
1242  
1243                  select @sqlcmd = 'select @retval=count(*) from '
1244                      + @dbname + '..syslogs '
1245                      + 'where op > 84'
1246                  exec (@sqlcmd)
1247                  if (@retval > 0)
1248                  begin --{
1249                      print "Error: Database '%1!' contains log records that will not be understood by the target server. Please, truncate the log and retry.", @orig_dbname
1250                      select @prepare_error_count = @prepare_error_count + 1
1251                  end --}
1252  
1253                  select @sqlcmd = 'select @pgcount=sum(size) from '
1254                      + 'master..sysusages where dbid=@dbid '
1255                      + 'and vdevno < 0 and location = 9'
1256                  exec (@sqlcmd)
1257                  if (@pgcount > 0)
1258                  begin --{
1259                      print "Error: Database '%1!' contains %2! hidden pages that have to be filled. Please, use ALTER DATABASE LOG ON command to extend the log %3! pages.", @orig_dbname, @pgcount, @pgcount
1260                      select @prepare_error_count = @prepare_error_count + 1
1261                  end --}
1262  
1263                  select @sqlcmd = 'select @pgcount=sum(size) from '
1264                      + 'master..sysusages where dbid=@dbid '
1265                      + 'and vdevno < 0 and location = 10'
1266                  exec (@sqlcmd)
1267                  if (@pgcount > 0)
1268                  begin --{
1269                      print "Error: Database '%1!' contains %2! hidden pages that have to be filled. Please, use ALTER DATABASE ON command to extend the data %3! pages.", @orig_dbname, @pgcount, @pgcount
1270                      select @prepare_error_count = @prepare_error_count + 1
1271                  end --}
1272  
1273                  select @sqlcmd = 'select @retval = count(1) from ' + @dbname
1274                      + '..syscolumns where id = object_id(''sysobjects'') and name = ''lobcomp_lvl'''
1275                  exec (@sqlcmd)
1276                  if (@retval > 0)
1277                  begin --{
1278                      /*
1279                      ** This block checks for LOB compressed columns.
1280                      ** syscolumns.status2 value 131072 is 0x20000,
1281                      ** "column should not be compressed".
1282                      ** syscolumns.sysstat3 value 2048 is 0x800,
1283                      ** "table contains compressed LOB data".
1284                      */
1285                      select @sqlclause = ' from '
1286                          + @dbname + '..syscolumns c,'
1287                          + @dbname + '..sysobjects o,'
1288                          + @dbname + '..syspartitions p'
1289                          + ' where o.id = c.id and o.id = p.id'
1290                          + ' and ((c.lobcomp_lvl is not null and c.status2 & 131072 = 0)'
1291                          + ' or  ((o.lobcomp_lvl is not null and o.lobcomp_lvl > 0) or (sysstat3 & 2048 != 0))'
1292                          + ' or  (p.lobcomp_lvl is not null and p.lobcomp_lvl > 0))'
1293                      select @sqlcmd = 'select @retval = count(1) ' + @sqlclause
1294                      exec (@sqlcmd)
1295                      if (@retval > 0)
1296                      begin --{
1297                          print "Error: Database '%1!' has tables configured for LOB compression.", @orig_dbname
1298                          -- Determine how wide the table name display must be.
1299                          select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
1300                          exec (@sqlcmd)
1301                          -- Display the affected table names.
1302                          select @sqlcmd = 'select distinct convert(varchar('
1303                              + convert(varchar(10), @retval)
1304                              + '),o.name) as ''Check these tables:'''
1305                              + @sqlclause
1306                          exec (@sqlcmd)
1307                          select @prepare_error_count = @prepare_error_count + 1,
1308                              @lob_comp_error = 1
1309                      end --}
1310                      /*
1311                      ** This block checks for compressed tables.
1312                      ** sysstat3 value 28672 is 0x7000, the three compression
1313                      ** status bits:
1314                      **  - 0x1000 = Table contains compressed data
1315                      **  - 0x2000 = Table is page level compressed
1316                      **  - 0x4000 = Table is row level compressed
1317                      */
1318                      select @sqlclause = ' from '
1319                          + @dbname + '..sysobjects o,'
1320                          + @dbname + '..syspartitions p'
1321                          + ' where o.id = p.id'
1322                          + '   and (o.sysstat3 & 28672 != 0'
1323                          + '    or (p.ptndcompver is not null and p.ptndcompver > 0))'
1324                      select @sqlcmd = 'select @retval = count(1) ' + @sqlclause
1325                      exec (@sqlcmd)
1326                      if (@retval > 0)
1327                      begin --{
1328                          print "Error: Database '%1!' has tables configured for compression.", @orig_dbname
1329                          -- Determine how wide the table name display must be.
1330                          select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
1331                          exec (@sqlcmd)
1332                          -- Display the affected table names.
1333                          select @sqlcmd = 'select distinct convert(varchar('
1334                              + convert(varchar(10), @retval)
1335                              + '),o.name) as ''Check these tables:'''
1336                              + @sqlclause
1337                          exec (@sqlcmd)
1338                          print "For each table, set compression = none, then use 'reorg rebuild' on that table."
1339                          select @prepare_error_count = @prepare_error_count + 1
1340                      end --}
1341                  end --}
1342  
1343                  select @sqlcmd = 'select @retval = count(1) from ' + @dbname
1344                      + '..syscolumns where id = object_id(''sysobjects'') and name = ''sysstat4'''
1345                  exec (@sqlcmd)
1346                  if (@retval > 0)
1347                  begin --{
1348                      /*
1349                      ** This block checks for compressed indexes.
1350                      ** Index compression status info are saved in
1351                      ** sysobjects.sysstat4
1352                      ** o 0x0001 = indexes on this table are compressed
1353                      ** sysindexes.status3
1354                      ** o 0x200 = this index is defined as compressed
1355                      ** o 0x400 = this index contains compressed data
1356                      ** and syspartitions.status
1357                      ** o 0x200 = index compression is enabled in this
1358                      **           index partition.
1359                      */
1360                      select @sqlclause = ' from '
1361                          + @dbname + '..sysobjects o,'
1362                          + @dbname + '..sysindexes i,'
1363                          + @dbname + '..syspartitions p '
1364                          + 'where o.id = i.id and o.id = p.id '
1365                          + 'and i.indid = p.indid '
1366                          + 'and (o.sysstat4 & 1 != 0 or '
1367                          + 'i.status3 & 1536 != 0 or '
1368                          + 'p.status & 512 != 0)'
1369                      select @sqlcmd = 'select @retval = count(1) ' + @sqlclause
1370                      exec (@sqlcmd)
1371                      if (@retval > 0)
1372                      begin --{
1373                          print "Error: Database '%1!' has tables containing indexes configured for index compression.", @orig_dbname
1374                          select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
1375                          exec (@sqlcmd)
1376                          select @sqlcmd = 'select distinct convert(varchar('
1377                              + convert(varchar(10), @retval)
1378                              + '), o.name) as ''check these tables'''
1379                              + @sqlclause
1380                          exec (@sqlcmd)
1381                          select @prepare_error_count = @prepare_error_count + 1
1382                      end --}
1383                  end --}
1384  
1385                  -- Check for 15.7 tables containing in-row LOBs
1386                  select @sqlclause = ' from '
1387                      + @dbname + '..sysobjects o,'
1388                      + @dbname + '..syscolumns c '
1389                      + 'where o.id = c.id and (inrowlen is not null) and (inrowlen > 0)'
1390                  select @sqlcmd = 'select @retval = count(1) ' + @sqlclause
1391                  exec (@sqlcmd)
1392                  if (@retval > 0)
1393                  begin --{
1394                      print "Error: Database '%1!' has tables containing in-row LOBs.", @orig_dbname
1395                      select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
1396                      exec (@sqlcmd)
1397                      select @sqlcmd = 'select distinct convert(varchar('
1398                          + convert(varchar(10), @retval)
1399                          + '), o.name) as ''check these tables'''
1400                          + @sqlclause
1401                      exec (@sqlcmd)
1402                      select @prepare_error_count = @prepare_error_count + 1
1403                  end --}
1404  
1405                  -- Check for replication filters
1406                  select @sqlcmd = 'select @retval = count(1) from ' + @dbname + '..sysobjects where
1407  		 		type = "RF"'
1408                  exec (@sqlcmd)
1409                  if (@retval > 0)
1410                  begin --{
1411                      print 'Database %1! contains replication filters. You must drop them before downgrade.', @orig_dbname
1412                      select @sqlcmd = 'select @retval = max(datalength(name)) from '
1413                          + @dbname + '..sysobjects where type = "RF"'
1414                      exec (@sqlcmd)
1415                      select @sqlcmd = 'select distinct convert(varchar('
1416                          + convert(varchar(10), @retval) + '), name)
1417  					 as ''Check these objects'' from ' +
1418                          @dbname + '..sysobjects where type = "RF"'
1419                      exec (@sqlcmd)
1420                  end --}
1421  
1422                  /*
1423                  ** If downgrading to a version prior to 15.7, check:
1424                  ** - There is no database that contains precomputed result sets.
1425                  **
1426                  ** So during 'prepare' we warn if there are databases having any
1427                  ** precomputed result set. And in 'verbose' mode we print out the 
1428                  ** names of all the precomputed result sets in each database.
1429                  */
1430  
1431                  if (not exists (select dbid from #baddb where dbid = @dbid))
1432                  begin --{
1433                      /* Initializations */
1434                      set @retval = null
1435                      select @dbname = db_name(@dbid)
1436                      select @orig_dbname = @dbname
1437                      /*
1438                      ** Database name in which there is tab or blank must
1439                      ** be delimited with '[]' in sql cmd.
1440                      */
1441                      if ((patindex(@tab_str, @dbname) > 0)
1442                              or (patindex(@blank_str, @dbname) > 0))
1443                      begin
1444                          select @dbname = '[' + @orig_dbname + ']'
1445                      end
1446  
1447                      select @sqlclause = ' from '
1448                          + @dbname + '..sysobjects where type = ''RS'' '
1449                      select @sqlcmd = 'select @retval = max(datalength(name)) ' + @sqlclause
1450                      exec (@sqlcmd)
1451  
1452                      if @retval is not null
1453                      begin --{
1454                          print "Database '%1!' (dbid: %2!) contains one or more precomputed result sets.", @orig_dbname, @dbid
1455  
1456                          select @sqlcmd = 'select convert(varchar(' + convert(varchar(10), @retval)
1457                              + '), name) as ''Drop these precomputed result sets:'' '
1458                              + @sqlclause
1459                          print ""
1460                          exec (@sqlcmd)
1461                          /*
1462                          ** At least one database has precomputed result set
1463                          ** created in it. Print a warning with the advice for
1464                          ** droping those before downgrade.
1465                          */
1466                          print ""
1467                          print "Error: All the precomputed result sets listed above should be dropped before downgrade."
1468                          print ""
1469  
1470                          select @prepare_error_count = @prepare_error_count + 1
1471  
1472                      end --}
1473                  end --}
1474  
1475                  /* Initializations */
1476                  set @retval = null
1477                  select @dbname = db_name(@dbid)
1478                  select @orig_dbname = @dbname
1479                  /*
1480                  ** Database name in which there is tab or blank must
1481                  ** be delimited with '[]' in sql cmd.
1482                  */
1483                  if ((patindex(@tab_str, @dbname) > 0)
1484                          or (patindex(@blank_str, @dbname) > 0))
1485                  begin
1486                      select @dbname = '[' + @orig_dbname + ']'
1487                  end
1488  
1489                  select @sqlclause = ' from ' + @dbname + '..sysattributes sattr where sattr.object_type="TI"'
1490                      + ' and sattr.object_info1 & 4096 = 4096'
1491                  select @sqlcmd = 'select @retval = max(datalength(sattr.object_cinfo2))' + @sqlclause
1492                  exec (@sqlcmd)
1493  
1494                  if @retval is not null
1495                  begin --{
1496                      print "Error: Database '%1!' has tables affected by no-datacopy-column drop.", @orig_dbname
1497  
1498                      --Show names of all the tables affected by no-datacopy-drop-column operation
1499                      select @sqlcmd = 'select distinct convert(varchar('
1500                          + convert(varchar(10), @retval)
1501                          + '),sattr.object_cinfo2) as ''Run Reorg Rebuild for these tables affected by no-datacopy-drop-column operation:'''
1502                          + @sqlclause
1503                      print ""
1504                      exec (@sqlcmd)
1505                      print ""
1506  
1507                      select @prepare_error_count = @prepare_error_count + 1
1508                  end --}
1509  
1510                  /*
1511                  ** If downgrading to a version prior to 15.7, check:
1512                  ** - There is no database that contains granular, dbcc 
1513                  **   cmd or select on builtin authmech permissions 
1514                  **   granted to UDRs (other than sa_serverprivs_role),
1515                  **   users (other then dbo) or groups. These permissions
1516                  **   will be deleted by downgrade and we want to display
1517                  **   these permissions to the user so that they can
1518                  **   use it to restore it later on.
1519                  **
1520                  ** So during 'prepare' we warn if there are databases 
1521                  ** having any of the above permissions and in 'verbose'
1522                  ** mode, we print out the list of of permissions in all
1523                  ** databases.
1524                  */
1525                  if (@verbose = 1)
1526                  begin --{
1527                      print "Checking if database '%1!' contains any granular permissions, DBCC command permissions or select permission on builtin authmech.", @orig_dbname
1528                  end --}
1529  
1530                  -- Populate temp table with users needed for display
1531                  select @sqlcmd = 'insert into #users(uid,name)'
1532                      + ' select u.uid, u.name from '
1533                      + @dbname + '.dbo.sysusers u'
1534                  exec (@sqlcmd)
1535  
1536                  -- Populate temp table with tables needed for display
1537                  select @sqlcmd = 'insert into #tables(id,objname)'
1538                      + ' select t.id, t.name from '
1539                      + @dbname + '.dbo.sysobjects t'
1540                  exec (@sqlcmd)
1541  
1542                  -- Check for GP permissions granted to grantees who are
1543                  -- not system roles, sa_serverprivs_role or dbo
1544                  select @sqlcmd = 'insert into #grantee(uid,name)'
1545                      + ' select distinct u.uid, u.name from '
1546                      + @dbname + '.dbo.sysusers u,'
1547                      + @dbname + '.dbo.sysroles r'
1548                      + ' where u.uid = 1'
1549                      + ' or (u.uid = r.lrid'
1550                      + ' and (r.id <= 31'
1551                      + ' or r.id = role_id(''sa_serverprivs_role'')))'
1552                  exec (@sqlcmd)
1553  
1554                  select @sqlcmd = 'insert into #perm(id,grantee,'
1555                      + 'grantee_name,action,type,grantor)'
1556                      + ' select distinct p.id, p.uid, u.name, p.action,'
1557                      + ' p.protecttype, p.grantor'
1558                      + ' from ' + @dbname + '.dbo.sysprotects p,'
1559                      + ' master.dbo.spt_values c,'
1560                      + @dbname + '.dbo.sysusers u'
1561                      + ' where p.action = c.number'
1562                      + ' and (c.type = ''GP'' or p.id = @authmech)'
1563                      + ' and c.number <= 1024'
1564                      + ' and p.uid not in (select uid from #grantee)'
1565                      + ' and p.uid = u.uid'
1566                  exec (@sqlcmd)
1567  
1568                  if (@dbid = 1)
1569                  begin --{
1570                      -- Check for master specific dbcc perms
1571                      -- as these will be also deleted later on
1572                      -- as they are not supported in < 15.7 ESD2.
1573                      select @sqlcmd = 'insert into #perm(id,grantee,'
1574                          + ' grantee_name,action,type,grantor)'
1575                          + ' select distinct p.id, p.uid, u.name,'
1576                          + 'p.action, p.protecttype, p.grantor'
1577                          + ' from ' + @dbname + '.dbo.sysprotects p,'
1578                          + ' master.dbo.spt_values c,'
1579                          + @dbname + '.dbo.sysusers u'
1580                          + ' where p.action = c.number'
1581                          + ' and c.type = ''T'''
1582                          + ' and c.number <= 1024'
1583                          + ' and c.msgnum & 16386 = 16386'
1584                          + ' and p.uid not in (select uid from #grantee)'
1585                          + ' and p.uid = u.uid'
1586                      exec (@sqlcmd)
1587                  end --}
1588  
1589                  if ((select count(*) from #perm) > 0)
1590                  begin --{
1591                      -- List permissions and exit
1592                      select distinct
1593                          grantor = u.name,
1594                          grantee = p.grantee_name,
1595                          type = case
1596                              when p.type = 0
1597                              then "Grant with grant option"
1598                              when p.type = 1
1599                              then "Grant"
1600                              when p.type = 2
1601                              then "Revoke"
1602                          end,
1603                          permission = a.name,
1604                          object = case
1605                              when p.id = 0
1606                              then ""
1607                              when (p.id = @authmech)
1608                              then (select c.name
1609                                  from master.dbo.spt_values c
1610                                  where c.number = p.id
1611                                      and c.type = "BI")
1612                              when (p.action = @identity_ins
1613                                      or p.action = @identity_upd)
1614                              then (select t.objname
1615                                  from #tables t
1616                                  where t.id = p.id)
1617                              else db_name(p.id)
1618                          end
1619                      into #permissions
1620                      from #perm p, master.dbo.spt_values a, #users u
1621                      where (a.type = "T" or a.type = "GP")
1622                          and a.number = p.action
1623                          and u.uid = p.grantor
1624  
1625                      print ""
1626                      print 'Database ''%1!'' contains permissions new to 15.7 granted to users, roles or groups.', @orig_dbname
1627                      print 'These permissions need to be dropped before downgrade. Use the displayed list of permissions to store the permissions so that they can be restored.'
1628                      print ""
1629  
1630                      exec sp_autoformat @fulltabname = #permissions
1631  
1632                      drop table #permissions
1633  
1634                      select @prepare_error_count = @prepare_error_count + 1
1635                  end --}
1636  
1637                  delete #users
1638                  delete #perm
1639                  delete #tables
1640                  delete #grantee
1641  
1642                  /* Initialization */
1643                  set @retval = null
1644                  select @dbname = db_name(@dbid)
1645                  select @orig_dbname = @dbname
1646                  /*
1647                  ** Database name in which there is tab or blank must
1648                  ** be delimited with '[]' in sql cmd.
1649                  */
1650                  if ((patindex(@tab_str, @dbname) > 0)
1651                          or (patindex(@blank_str, @dbname) > 0))
1652                  begin
1653                      select @dbname = '[' + @orig_dbname + ']'
1654                  end
1655  
1656                  /* Check for existence of deferred tables in this database */
1657                  select @sqlclause = ' from ' + @dbname + '.dbo.sysobjects o '
1658                      + 'where o.type = ''U'' and o.sysstat3 & 128 = 128'
1659                  select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
1660                  exec (@sqlcmd)
1661                  if @retval is not null
1662                  begin --{
1663                      print "Error: Database '%1!' (dbid: %2!) has one or more deferred tables.", @orig_dbname, @dbid
1664  
1665                      /* Display the names of all the deferred tables in this database */
1666                      select @sqlcmd = 'select convert(varchar(' + convert(varchar(10), @retval)
1667                          + '),o.name) as ''Materialize or drop the following deferred tables:'' '
1668                          + @sqlclause
1669                      exec (@sqlcmd)
1670                      print ""
1671  
1672                      select @prepare_error_count = @prepare_error_count + 1
1673                  end --} 
1674  
1675                  /*
1676                  ** Check for existence of tables configured explicitly not
1677                  ** to deallocate FTP after NULL update in this database
1678                  */
1679                  select @sqlclause = ' from ' + @dbname + '.dbo.sysobjects o '
1680                      + 'where o.sysstat2 & -2147483648 = -2147483648'
1681                  select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
1682                  exec (@sqlcmd)
1683                  if @retval is not null
1684                  begin --{
1685                      print "Error: Database '%1!' (dbid: %2!) has tables configured not to deallocate first text page after NULL update.", @orig_dbname, @dbid
1686  
1687                      /* Display the names of all the tables with such setting in this database */
1688                      select @sqlcmd = 'select convert(varchar(' + convert(varchar(10), @retval)
1689                          + '),o.name) as ''Run sp_chgattribute to change dealloc_firxt_txt_pg to 0 or 1 for following tables:'' '
1690                          + @sqlclause
1691                      exec (@sqlcmd)
1692                      print ""
1693  
1694                      select @prepare_error_count = @prepare_error_count + 1
1695                  end --} 
1696  
1697                  -- Bit IND_RID_VALUECMP(256) in sysindexes.status
1698                  -- indicates the index using RID value comparision.
1699                  --
1700                  select @sqlcmd = 'select @retval=count(*) from '
1701                      + @dbname + '..sysindexes where ((status & 256) = 256) '
1702                  exec (@sqlcmd)
1703                  if (@retval > 0)
1704                  begin --{
1705                      print "Error: Database '%1!' has indexes using RID value comparison. Please drop these indexes and retry.", @orig_dbname
1706  
1707                      if (@verbose = 1)
1708                      begin --{
1709                          -- Display the names of all the indexes
1710                          -- with such setting in this databases.
1711                          set @sqlcmd = @dbname + ".dbo.sp_dwngd_helpindex"
1712                          exec @sqlcmd
1713                          print ""
1714                      end --}
1715  
1716                      select @prepare_error_count = @prepare_error_count + 1
1717                  end --}
1718  
1719                  /*
1720                  ** Does db have logrecs from parallel NTAs in the active log
1721                  ** region? That will prevent loading this db / tran dump into
1722                  ** SP100 or prior. As we are in single-user mode, and have
1723                  ** already done a checkpoint, this should never fail.
1724                  */
1725                  if (loginfo(@dbname, 'wl_has_pll_ntas_alr') != 0)
1726                  begin
1727  
1728                      print "Error: Database '%1!' contains log records that will not be understood by the target server. Please, truncate the log and retry.", @orig_dbname
1729  
1730                      if (@verbose = 1)
1731                      begin
1732                          select loginfo(@dbname, 'wl_has_pll_ntas_alr')
1733                              as pll_ntas_in_alr
1734                              , loginfo(@dbname, 'wl_has_pll_ntas')
1735                              as pll_ntas_in_log
1736                      end
1737  
1738                      select @prepare_error_count = @prepare_error_count + 1
1739                  end
1740                  else if (@verbose = 1)
1741                  begin
1742                      print "Transaction log in database '%1!' is clear of parallel NTA activity."
1743                          , @orig_dbname
1744                  end
1745  
1746                  fetch sysdbc0 into @dbid, @dbname
1747              end --}
1748              close sysdbc0
1749              deallocate cursor sysdbc0
1750  
1751              if (@lob_comp_error != 0)
1752              begin --{
1753                  print "*** NOTE about tables using LOB compression:"
1754                  print "To reset table LOB compression status indicators, copy data from the indicated tables into new tables, then drop the old tables."
1755                  print "***"
1756              end --}
1757          end --}
1758  
1759          /*
1760          ** If downgrading to a version prior to 15.7, check:
1761          ** - There are no databases larger than 2^31 logical pages
1762          */
1763          if (@toversid < 15700)
1764          begin --{
1765              select @hi_db_size = 2
1766              select @hi_db_size = power(@hi_db_size, 31)
1767              if exists (select 1 from master.dbo.sysusages
1768                      where lstart + size >= @hi_db_size)
1769              begin
1770                  select @dbsz = 2
1771                  select @dbsz = power(@dbsz, 31)
1772                  select @ppgb = (1024 * 1024 * 1024) / @@maxpagesize
1773                  select @dbsz = @dbsz / @ppgb
1774                  print 'One or more databases in this installation is too large to downgrade.'
1775                  print 'Databases %1! Gb or larger must be dropped before downgrade.', @dbsz
1776                  select distinct db_name(dbid) as 'Check database:'
1777                  from master.dbo.sysusages
1778                  where lstart + size >= @hi_db_size
1779                  select @prepare_error_count = @prepare_error_count + 1
1780              end
1781  
1782          end --}
1783  
1784          /*
1785          ** If downgrading to a version prior to Carina (15.7 ESD#5), check:
1786          ** - There are no database objects in between reorg defrag runs.
1787          ** - There are no databases having suspect replication indexes. (These
1788          **   might be turned off by text back-linking. Older servers don't
1789          **   understand that, so we disallow suspect replication indexes.)
1790          ** - The config option 'max utility parallel degree' is not set to a
1791          **   value > its DEFAULT value (i.e 1), else issue a warning.
1792          */
1793          if (@toversid <= 15699)
1794          begin --{
1795              declare sysdbc0 cursor for
1796              select dbid, name from master.dbo.sysdatabases a
1797              where status3 <> 256
1798                  and not exists (select 1 from #baddb b
1799                      where a.dbid = b.dbid)
1800  
1801              open sysdbc0
1802              fetch sysdbc0 into @dbid, @dbname
1803  
1804              while (@@sqlstatus = 0) -- and @dbid > 3)
1805              begin --{
1806                  select @orig_dbname = @dbname
1807                  /*
1808                  ** Database name in which there is tab or blank must
1809                  ** be delimited with '[]' in sql cmd.
1810                  */
1811                  if ((patindex(@tab_str, @dbname) > 0)
1812                          or (patindex(@blank_str, @dbname) > 0))
1813                  begin
1814                      select @dbname = '[' + @orig_dbname + ']'
1815                  end
1816  
1817                  if (@verbose = 1)
1818                  begin
1819                      print 'Checking database ''%1!'' for defrag downgrade readiness.', @orig_dbname
1820                  end
1821  
1822                  set @retval = null
1823                  select @sqlclause = 'select @maxlen = max(datalength(name)) '
1824                      + ' from ' + @dbname
1825                      + '..sysobjects so where so.id in (select distinct sa.object'
1826                  select @sqlbuf = ' from ' + @dbname + '..sysattributes sa where sa.class = 26 and sa.attribute = 2'
1827                  select @sqlcmd = @sqlclause + @sqlbuf + ')'
1828                  exec (@sqlcmd)
1829                  select @sqlclause = 'select convert(varchar('
1830                      + convert(varchar(10), @maxlen) + '), name) as '
1831                      + '''Run dbcc zapdefraginfo  for these tables affected by reorg defrag.'' '
1832                      + ' from ' + @dbname
1833                      + '..sysobjects so where so.id in (select distinct sa.object'
1834                  select @sqlcmd = 'select @retval = count(distinct sa.object)'
1835                      + @sqlbuf
1836                  exec (@sqlcmd)
1837  
1838                  if @retval != 0
1839                  begin --{
1840                      print "Error: Database '%1!' has tables affected by reorg defrag.", @orig_dbname
1841  
1842                      --Show names of all the tables affected by reorg defrag in progress.
1843  
1844                      select @sqlcmd = @sqlclause + @sqlbuf + ')'
1845  
1846                      print ""
1847                      exec (@sqlcmd)
1848                      print ""
1849  
1850                      select @prepare_error_count = @prepare_error_count + 1
1851                  end --}
1852                  fetch sysdbc0 into @dbid, @dbname
1853              end --}
1854              close sysdbc0
1855              deallocate cursor sysdbc0
1856  
1857              -- Check for suspect replication indexes.
1858              -- 0x8000(32768) on sysindexes.status means index is suspect.
1859              -- 0x20(32) on sysindexes.status3 means it's replication index.
1860              declare sysdbc0 cursor for
1861              select dbid, name from master.dbo.sysdatabases a
1862              where not exists (select 1 from #baddb b
1863                      where a.dbid = b.dbid)
1864              open sysdbc0
1865              fetch sysdbc0 into @dbid, @dbname
1866              while (@@sqlstatus = 0) -- and @dbid > 3)
1867              begin --{
1868                  select @orig_dbname = @dbname
1869                  /*
1870                  ** Database name in which there is tab or blank must
1871                  ** be delimited with '[]' in sql cmd.
1872                  */
1873                  if ((patindex(@tab_str, @dbname) > 0)
1874                          or (patindex(@blank_str, @dbname) > 0))
1875                  begin
1876                      select @dbname = '[' + @orig_dbname + ']'
1877                  end
1878  
1879                  select @sqlcmd = 'select @c = count(id) from ' + @dbname
1880                      + '.dbo.sysindexes where status & 32768 != 0 '
1881                      + 'and status3 & 32 != 0'
1882                  exec (@sqlcmd)
1883                  if (@c != 0)
1884                  begin --{
1885                      print 'Error: Database ''%1!'' contains suspect replication indexes that must be dropped via DBCC REINDEX before downgrade.', @orig_dbname
1886                      select @sqlcmd = 'select @c = max(datalength(o.name)) '
1887                          + '+ max(datalength(i.name)) + 1 '
1888                          + 'from ' + @dbname + '.dbo.sysobjects o,'
1889                          + @dbname + '.dbo.sysindexes i '
1890                          + 'where i.id = o.id and i.status & 32768 != 0 '
1891                          + 'and i.status3 & 32 != 0'
1892                      exec (@sqlcmd)
1893                      select @sqlcmd = 'select convert(varchar(' + convert(varchar(10), @c)
1894                          + '), o.name + ''.'' + i.name) as ''Drop these indexes:'''
1895                          + 'from ' + @dbname + '.dbo.sysobjects o,'
1896                          + @dbname + '.dbo.sysindexes i '
1897                          + 'where i.id = o.id and i.status & 32768 != 0 '
1898                          + 'and i.status3 & 32 != 0'
1899                      print ''
1900                      exec (@sqlcmd)
1901                      print ''
1902  
1903                      select @prepare_error_count = @prepare_error_count + 1
1904                  end --}
1905                  fetch sysdbc0 into @dbid, @dbname
1906              end --}
1907              close sysdbc0
1908              deallocate cursor sysdbc0
1909  
1910              -- Warn user if max utility parallel degree value in database master > its DEFAULT value (i.e 1)
1911              begin --{
1912                  select @sqlcmd = 'select @c = value from master.dbo.sysconfigures where config=587'
1913                  exec (@sqlcmd)
1914                  if (@c > 1)
1915                  begin --{
1916                      if (@verbose = 1)
1917                      begin
1918                          print 'Checking for config parameter downgrade readiness.'
1919                      end
1920                      print 'Warning: Config parameter ''max utility parallel degree'' is set to ''%1!'' ', @c
1921                      print ''
1922                      print 'This parameter will be removed after downgrade.'
1923                      print ''
1924                      print 'Please set ''max parallel degree'' accordingly for CREATE INDEX and UPDATE STATISTICS to run in parallel'
1925                      print ''
1926                  end --}
1927              end --}
1928          end --}
1929  
1930          if @prepare_error_count != 0
1931          begin
1932              print " "
1933              print "sp_downgrade '%1!' failed.", @cmd
1934              print "Re-run sp_downgrade after addressing the items listed above."
1935              return @prepare_error_count
1936          end
1937  
1938          if (@cmd = 'prepare')
1939          begin
1940              /*
1941              ** In prepare mode the rest of non password/encryption
1942              ** processing is done, as there is nothing else to verify. 
1943              */
1944              print 'sp_downgrade ''prepare'' completed.'
1945              return 0
1946          end
1947  
1948          select @alldbs = 0,
1949              @allbutmasterdb = - 1,
1950              @masterdb = db_id('master'),
1951              @sybsecuritydb = db_id('sybsecurity'),
1952              @downgrade_passwords_sql = 'sp_passwordpolicy ''downgrade''',
1953              @update_sysprocedures_sql =
1954              'update sysprocedures ' +
1955              'set status = status | 0x8 ' +
1956              'where sequence = 0 ',
1957              @drop_1502_sysoptions_sql =
1958              'delete syscolumns where id = 57 ' +
1959              'delete sysindexes where id = 57 ' +
1960              'delete sysobjects where id = 57 ' +
1961              'delete syspartitions where id = 57 ' +
1962              'delete systabstats where id = 57 ' +
1963              'select @d=rm_anchor(''master'',''table'', 57)',
1964              @dbinfo_reset_asevers_sql =
1965              'declare @d int ' +
1966              'select @d=dbinfo_update(@dbid, ''ASEvers'', ' +
1967              convert(varchar(10), @toversid) + ')',
1968              /* 32 as 0x00000002 indicates downgrade is needed */
1969              @dbinfo_turnon_downgradeneed_sql =
1970              'declare @stat4 int ' +
1971              'select @stat4=convert(int, dbinfo_get(''master'',''status4'')) ' +
1972              'select @d=dbinfo_update(1, ''status4'', 32 | @stat4)',
1973              @dbinfo_update_1550_master_sql =
1974              'select @d=dbinfo_update(1, ''upgd_minor'', 1620)',
1975              @dbinfo_update_1502_master_sql =
1976              'select @d=dbinfo_update(1, ''upgd_minor'', 1560)',
1977              @dbinfo_update_15015_alldbs_sql =
1978              'select @d=dbinfo_update(@dbid, ''upgd_minor'', 1700)',
1979              @downgrade_sysconfigures_15015_sql1 =
1980              'delete sysconfigures where name in (' +
1981              '''config file version'',''max query parallel degree'',' +
1982              '''cost of a logical io'',''cost of a physical io'',' +
1983              '''cost of a cpu unit'',''auto query tuning'',' +
1984              '''enable query tuning mem limit'',' +
1985              '''query tuning plan executions'',' +
1986              '''enable query tuning time limit'',' +
1987              '''max buffers per lava operator'',' +
1988              '''enable merge join'',''mnc_full_index_filter'')',
1989              @downgrade_sysconfigures_15015_sql2 =
1990              'delete sysconfigures where name in (' +
1991              '''engine memory log size'',''compression memory size'',' +
1992              '''enable sql debugger'',''identity reservation size'',' +
1993              '''session tempdb log cache size'',''max nesting level'',' +
1994              '''enable pci'',''max pci slots'',' +
1995              '''pci memory size'',' +
1996              '''restricted decrypt permission'',''net password encryption reqd'')',
1997              @downgrade_sysconfigures_15015_sql3 =
1998              'delete sysconfigures where name in (' +
1999              '''start xp server during boot'',''enable stmt cache monitoring'')',
2000              @downgrade_sysconfigures_15015_cmd =
2001              'if not exists (select 1 from sysconfigures where config = 392) ' +
2002              'begin ' +
2003              'insert sysconfigures(config,value,comment,status,name,parent) values(' +
2004              '392,0,''enable enterprise java beans'',262153,''enable enterprise java beans'',39) ' +
2005              'delete master..sysattributes where class = 1 and attribute = 1560 ' +
2006              'end',
2007              /* 1502 upgd_minor for user tables is still 1554 */
2008              @dbinfo_update_1550_userdb_sql =
2009              'select @d=dbinfo_update(@dbid, ''upgd_minor'', 1554)',
2010              @dbinfo_update_1502_userdb_sql =
2011              'select @d=dbinfo_update(@dbid, ''upgd_minor'', 1554)',
2012              @dbinfo_update_1500_master_sql =
2013              'select @d=dbinfo_update(1, ''upgd_minor'', 1550)',
2014              @dbinfo_update_1500_userdb_sql =
2015              'select @d=dbinfo_update(@dbid, ''upgd_minor'', 1554)',
2016              @reset_sysservers_srvnetname_len =
2017              'update syscolumns ' +
2018              'set length=32 ' +
2019              'where id=40 and colid=4 and length>32',
2020              @downgrade_15015_config = "downgrade_15015_config",
2021              @downgrade_15015_msg = "@downgrade_15015_msg",
2022              @sysobjstat3_sql = "sysobjstat3_sql",
2023              @durability_sql = "UNUSED",
2024              @downgrade_sysauditoptions_sql =
2025              'delete from sybsecurity..sysauditoptions where ' +
2026              'name in (''login_locked'', ''password'', ' +
2027              '''keycustodian_role'', ''errorlog'')',
2028              @downgrade_dump_config = 'exec sp_downgrade_dump_config @show_cmd',
2029              @downgrade_sysdams = 'exec sp_downgrade_sysdams null, ' +
2030              'downgrade, @show_cmd'
2031  
2032  
2033          /*
2034          ** Get rid of the explicit builtin and dbcc permissions which have
2035          ** been granted to sa_role and dbo. Else while upgrading to 15.7 ESD2,
2036          ** we will get duplicate key insertion error as upgrade adds these
2037          ** explicit permissions. Also, convert any builtin and dbcc permissions
2038          ** granted to other system roles to the format < 15.7 ESD2.
2039          */
2040          if (@toversid < 15700)
2041          begin --{
2042              /*
2043              ** Create a temporary table which holds all the info on 
2044              ** permissions for builtin functions 
2045              */
2046              create table #perm_bi_t_tab(uid int not null,
2047                  grantor int not null,
2048                  ptype tinyint not null)
2049  
2050              /*
2051              ** Create a temporary table which holds all the info on 
2052              ** permissions for dbcc commands 
2053              */
2054              create table #perm_dbcc_t_tab(uid int not null,
2055                  grantor int not null)
2056  
2057              /* A cursor to traverse sysdatabases */
2058              declare sysdbc1 cursor for
2059              select dbid from #sysdatabases
2060  
2061              open sysdbc1
2062              fetch sysdbc1 into @dbid
2063              while (@@sqlstatus = 0)
2064              begin -- {
2065                  select @dbname = db_name(@dbid)
2066                  select @orig_dbname = @dbname
2067                  /*
2068                  ** Database name in which there is tab or blank must
2069                  ** be delimited with '[]' in sql cmd.
2070                  */
2071                  if ((patindex(@tab_str, @dbname) > 0)
2072                          or (patindex(@blank_str, @dbname) > 0))
2073                  begin
2074                      select @dbname = '[' + @orig_dbname + ']'
2075                  end
2076  
2077                  if (@dbid = 1)
2078                  begin --{
2079                      /* 
2080                      ** Delete the BI permissions granted to sa_role
2081                      */
2082                      select @sqlcmd = 'delete master.dbo.sysprotects'
2083                          + ' from master.dbo.sysprotects p,'
2084                          + ' master.dbo.sysroles r'
2085                          + ' where p.uid = r.lrid'
2086                          + ' and r.id = role_id(''sa_role'')'
2087                          + ' and p.id in (81,82,83,84)'
2088                      exec (@sqlcmd)
2089  
2090                      /* 
2091                      ** Delete the any DBCC permissions granted to 
2092                      ** sa_role 
2093                      */
2094                      select @sqlcmd = 'delete master.dbo.sysprotects'
2095                          + ' from master.dbo.sysprotects p,'
2096                          + ' master.dbo.spt_values v,'
2097                          + ' master.dbo.sysroles r'
2098                          + ' where p.action = v.number'
2099                          + ' and r.lrid = p.uid'
2100                          + ' and r.id = role_id(''sa_role'')'
2101                          + ' and v.type = ''T'''
2102                          + ' and (v.msgnum & 16385 = 16385)'
2103                      exec (@sqlcmd)
2104                  end --}
2105                  else
2106                  begin --{
2107                      /*
2108                      ** Delete the database specific DBCC permissions
2109                      ** granted to dbo
2110                      */
2111                      select @sqlcmd = 'delete ' + @dbname + '.dbo.sysprotects'
2112                          + ' from ' + @dbname + '.dbo.sysprotects p,'
2113                          + ' master.dbo.spt_values v'
2114                          + ' where p.action = v.number'
2115                          + ' and p.uid = 1'
2116                          + ' and v.type = ''T'''
2117                          + ' and (v.msgnum & 16386 = 16386)'
2118                      exec (@sqlcmd)
2119                  end --}
2120  
2121                  select @sqlcmd = 'insert into #perm_bi_t_tab'
2122                      + ' select distinct uid, grantor, protecttype'
2123                      + ' from ' + @dbname + '.dbo.sysprotects'
2124                      + ' where id in (81,82,83,84)'
2125                      + ' order by uid, grantor, protecttype'
2126                  exec (@sqlcmd)
2127  
2128                  select @sqlcmd = 'insert into #perm_dbcc_t_tab'
2129                      + ' select distinct uid, grantor'
2130                      + ' from ' + @dbname + '.dbo.sysprotects, master.dbo.spt_values'
2131                      + ' where action = number'
2132                      + ' and type = ''T'''
2133                      + ' and (msgnum & 16386 = 16386 or msgnum & 16385 = 16385)'
2134                      + ' order by uid, grantor'
2135                  exec (@sqlcmd)
2136  
2137                  /*
2138                  ** Convert builtin permissions to < 15.7 ESD2 format
2139                  ** - There will be only one row for all builtin perms
2140                  **   granted to a particular grantee. So many rows in 
2141                  **   ESD2 will have to be converted to one row
2142                  ** - sysprotects.id in ESD2 has the actual builtin id 
2143                  **   for FGAC builtins. We need to insert BUILTIN_ID 
2144                  **   which is -255 for id column
2145                  ** - In ESD2, column map in sysprotects is null. But 
2146                  **   the builtins will have to be converted to bitmap 
2147                  **   in the column map
2148                  ** - In ESD2, sysprotects.action has PERM_SELECT_BI 
2149                  **   which is 5 whereas in < ESD2, it is SELECT.
2150                  **
2151                  ** Convert dbcc permissions to < 15.7 ESD2 format
2152                  ** - There will be only one row for all dbcc permissions
2153                  **   granted to a particular grantee. So many rows in 
2154                  **   ESD2 will have to be converted to one row
2155                  ** - sysprotects.id in ESD2 has 0. It has to be changed
2156                  **    to -317
2157                  ** - In ESD2, column map in sysprotects is null. But 
2158                  **   the dbcc cmd will have to be converted to bitmap 
2159                  **   in the column map
2160                  ** - In ESD2, sysprotects.action has the actual 
2161                  **   permission associated with the DBCC commamnd 
2162                  **   whereas in < ESD2, it is 317.
2163                  */
2164                  if (exists (select * from #perm_bi_t_tab)
2165                          or exists (select * from #perm_dbcc_t_tab))
2166                  begin
2167                      dbcc downgd_bi_dbcc_perms(@orig_dbname,
2168                          "#perm_bi_t_tab", "#perm_dbcc_t_tab")
2169                  end
2170  
2171                  delete #perm_bi_t_tab
2172                  delete #perm_dbcc_t_tab
2173  
2174                  fetch sysdbc1 into @dbid
2175              end --}
2176  
2177              close sysdbc1
2178              deallocate cursor sysdbc1
2179          end --}
2180  
2181          /*
2182          ** The following is a table which will contain various actions
2183          ** to perform on downgrade.  The 'toversion' is either "any
2184          ** version" or the highest ASE version at which the given action
2185          ** is needed during downgrade.
2186          */
2187          create table #actions
2188          (
2189              actionid int,
2190              dbid int,
2191              toversion int,
2192              action varchar(1024),
2193              errorlog_msg varchar(128)
2194          )
2195  
2196          -- Downgrade login passwords if going to SMP version <= 15.0.1 or SDC version 15.0.1.5
2197          insert #actions values (1, @masterdb, 15015,
2198              @downgrade_passwords_sql,
2199              'Downgrade : Downgrading login passwords.')
2200  
2201          -- Mark stored procs for recompile on any downgrade
2202          insert #actions values (2, @alldbs, @any15versid,
2203              @update_sysprocedures_sql,
2204              'Downgrade : Marking stored procedures to be recreated from text.')
2205  
2206          -- Remove sysoptions if going to <= 15.0.1
2207          insert #actions values (3, @masterdb, 15015,
2208              @drop_1502_sysoptions_sql,
2209              'Downgrade : Dropping Sysoptions system table.')
2210  
2211          -- Downgrade systabstats if going to <= 15.0.1
2212          insert #actions values (4, @alldbs, 15015,
2213              'if exists (select 1 from :DBNAME:..syscolumns ' +
2214              'where id = 23 and name = ''pioclmdata'') ' +
2215              'begin ' +
2216              'delete :DBNAME:..syscolumns ' +
2217              'where id = 23 ' +
2218              '  and name in (''pioclmdata'', ''pioclmindex'', ' +
2219              '''piocsmdata'', ''piocsmindex'') ' +
2220              'plan ''(delete(i_scan csyscolumns '
2221              + ':DBNAME:..syscolumns))'' ' +
2222              'insert into :DBNAME:..syscolumns ' +
2223              '(id, number, colid, status, type, length, offset, ' +
2224              'usertype, cdefault, domain, name, status3) ' +
2225              'values(23, 0, 19, 0, 62, 8, 112, ' +
2226              '8, 0, 0, ''spare2'', 0) ' +
2227              'insert into :DBNAME:..syscolumns ' +
2228              '(id, number, colid, status, type, length, offset, ' +
2229              'usertype, cdefault, domain, name, status3) ' +
2230              'values(23, 0, 27, 0, 62, 8, 144, ' +
2231              '8, 0, 0, ''spare4'', 0) ' +
2232  
2233              'update :DBNAME:..syscolumns ' +
2234              'set name = ''spare5'' ' +
2235              'where id = 23 and name = ''warmcachepgcnt'' ' +
2236              'end',
2237              'Downgrade : Downgrading Systabstats system table.')
2238  
2239          -- Downgrade sysconfigures if going to 15.0.1.5
2240          insert #actions values (5, @masterdb, 15015, @downgrade_15015_config,
2241              'Downgrade : Updating 15.0.1.5 configuration parameters.')
2242  
2243          -- Downgrade sysmessages if going to 15.0.1.5
2244          insert #actions values (6, @masterdb, 15015, @downgrade_15015_msg,
2245              'Downgrade : Updating 15.0.1.5 sysmessages.')
2246  
2247          -- Revert the length of sysservers.srvnetname from 255 to 32
2248          insert #actions values (7, @masterdb, 15020,
2249              @reset_sysservers_srvnetname_len,
2250              'Downgrade : Resetting max length of sysservers.srvnetname')
2251  
2252          -- Downgrade sysprocedures if going to < 15.0.3
2253          insert #actions values (8, @alldbs, 15020,
2254              'update :DBNAME:..sysprocedures ' +
2255              'set qp_setting = null ' +
2256              'where qp_setting is not null ' +
2257              'delete :DBNAME:..syscolumns ' +
2258              'where id = 5 and ' +
2259              'name = ''qp_setting''',
2260              'Downgrade : Downgrading Sysprocedures system table.')
2261  
2262          -- Downgrade sysqueryplans if going to < 15.0.3
2263          insert #actions values (9, @alldbs, 15020,
2264              'update :DBNAME:..sysqueryplans ' +
2265              'set dbid = null, qpdate = null, sprocid = null, ' +
2266              'hashkey2 = null, key1 = null, key2 = null, ' +
2267              'key3 = null, key4 = null ' +
2268              'where dbid is not null ' +
2269              'or qpdate is not null ' +
2270              'or sprocid is not null ' +
2271              'or hashkey2 is not null ' +
2272              'or key1 is not null ' +
2273              'or key2 is not null ' +
2274              'or key3 is not null ' +
2275              'or key4 is not null ' +
2276              'delete :DBNAME:..syscolumns ' +
2277              'where id = 27 and name in (''dbid'', ''qpdate'', ' +
2278              '''sprocid'', ''hashkey2'', ' +
2279              '''key1'', ''key2'', ''key3'', ''key4'')',
2280              'Downgrade : Downgrade Sysqueryplans system table.')
2281  
2282          -- Remove some config params if going to < 15.0.3
2283          insert #actions values (10, @masterdb, 15020,
2284              'delete from master.dbo.sysconfigures where config > 500',
2285              'Downgrade : Removing pre-15.0.3 configuration parameters.')
2286  
2287          -- Remove some config params if going to < 15.5
2288          insert #actions values (11, @masterdb, 15035,
2289              'delete from sysconfigures where config > 508',
2290              'Downgrade : Removing pre-15.5 configuration parameters.')
2291  
2292          insert #actions values (12, @masterdb, 15035,
2293              @durability_sql,
2294              'Downgrade : Removing durability column in sysdatabases.')
2295  
2296          -- Remove types 'bigdatetime', 'bigtime' if goint to < 15.5
2297          insert #actions values (13, @alldbs, 15035,
2298              'delete :DBNAME:..systypes where name in ' +
2299              '(''bigdatetime'',''bigtime'', ''bigdatetimen'', ''bigtimen'')',
2300              'Downgrade : Removing types ''bigdatetime'' and ''bigtime''')
2301  
2302          -- Downgrade sysobjects if going to < 15.5
2303          insert #actions values (14, @masterdb, 15035,
2304              @sysobjstat3_sql,
2305              'Downgrade : Downgrade Sysobjects system table.')
2306  
2307          -- Remove index sysdevices.ncsysdevices on sysdevices if going to any SMP < 15.5
2308          -- Exception: 15015 is the only SDC version which is below 15030.
2309          if (@toversid != 15015)
2310          begin --{
2311              insert #actions values (15, @masterdb, 15030,
2312                  'set switch on drop_system_index  with override ' +
2313                  'drop index sysdevices.ncsysdevices ' +
2314                  'set switch off drop_system_index ',
2315                  'Downgrade : Removing index sysdevices.ncsysdevices on sysdevices.')
2316          end --}		
2317  
2318          -- Downgrade sysauditoptions if going to <= 15.0.1
2319          if (@sybsecuritydb is not null)
2320          begin --{
2321              insert #actions values (16, @sybsecuritydb, 15010,
2322                  @downgrade_sysauditoptions_sql,
2323                  'Downgrade : Downgrading global auditing options.')
2324          end --}
2325  
2326          -- Remove full logging modes from sysattributes in any version < 15.7
2327          insert #actions values (17, @masterdb, 15500,
2328              'delete sysattributes where class = 38',
2329              'Downgrade : Removing full logging modes from sysattributes.')
2330  
2331          -- Downgrade data-only locked table rows if going to <= 15.5.6
2332          insert #actions values (18, @alldbs, 15560,
2333              'declare @ret int '
2334              + 'select @ret = dol_downgrade_check('':DBNAME:'', @toversid) '
2335              + 'print "Database :DBNAME: table downgrade status: %1!", @ret '
2336              + 'if @ret != 0 '
2337              + 'begin '
2338              + '  print "*** Tables in database '':DBNAME:'' cannot be downgraded." '
2339              + '  print "*** See the server error log for details." '
2340              + '  select @exec_error_count = @exec_error_count + 1 '
2341              + 'end '
2342              , 'Downgrade : Downgrading data-only locked table rows.')
2343  
2344          -- Remove column sysoptions.number in any version < 15.7
2345          insert #actions values (19, @masterdb, 15699,
2346              'delete syscolumns where id = object_id(''sysoptions'') and name=''number''',
2347              'Downgrade : Removing column sysoptions.number.')
2348  
2349          -- Remove srvprincipal column from sysservers table if going to <= SMP 15.5 or SDC 15.5.0.5
2350          insert #actions values (20, @masterdb, 15505,
2351              'delete syscolumns where id = object_id(''sysservers'') ' +
2352              'and name = ''srvprincipal''',
2353              'Downgrade : Removing srvprincipal column from sysservers system table')
2354  
2355          -- Remove 'automatic master key access' config params if going to <= 15.5
2356          insert #actions values (21, @masterdb, 15505,
2357              'delete sysconfigures where config = 503',
2358              'Downgrade : Removing ''automatic master key access'' configuration parameter.')
2359  
2360          -- Remove sysattribute values if going to <= 15.5
2361          insert #actions values (22, @masterdb, 15505,
2362              'delete sysattributes where class = 25 and attribute in (2, 3)',
2363              'Downgrade : Removing DualControl sysattribute rows')
2364  
2365          -- Downgrade sysattributes system catalog if going to <= 15.5
2366          insert #actions values (23, @alldbs, 15505,
2367              'update :DBNAME:..sysattributes ' +
2368              'set object_cinfo2 = null, object_datetime = null ' +
2369              'where object_cinfo2 is not null ' +
2370              'or object_datetime is not null ' +
2371              'delete :DBNAME:..syscolumns ' +
2372              'where id = 21 and name in (''object_cinfo2'', ' +
2373              '''object_datetime'')',
2374              'Downgrade : Downgrading sysattributes system table.')
2375  
2376          -- Downgrade syscomments system catalog if going to <= 15.5
2377          insert #actions values (24, @alldbs, 15505,
2378              'update :DBNAME:..syscomments ' +
2379              'set encrkeyid = null ' +
2380              'where encrkeyid is not null ' +
2381              'delete :DBNAME:..syscolumns ' +
2382              'where id = 6 and name = ''version'' ' +
2383              'delete :DBNAME:..syscolumns ' +
2384              'where id = 6 and name =''encrkeyid''',
2385              'Downgrade : Downgrading syscomments system table.')
2386  
2387          -- Truncate password and lock role if going to <= SMP 15.5 or SDC 15.5.0.5
2388          insert #actions values (25, @masterdb, 15505,
2389              'delete statistics syssrvroles(password) ' +
2390              'if exists (select 1 from syssrvroles where password is not null) ' +
2391              'begin ' +
2392              'print "Truncating password and locking following role(s)" ' +
2393              'select name from syssrvroles where password is not null ' +
2394              'update syssrvroles set password = null, status = (status | @lockrole) ' +
2395              'where password is not null ' +
2396              'end ' +
2397              'update syssrvroles set locksuid = null, lockreason = null, lockdate = null ' +
2398              'where locksuid is not null ' +
2399              'or lockreason is not null ' +
2400              'or lockdate is not null ' +
2401              'delete syscolumns where id = object_id(''syssrvroles'') ' +
2402              'and name in (''locksuid'', ''lockreason'', ''lockdate'')',
2403              'Downgrade : Truncated role password, locked role and ' +
2404              'removed columns locksuid, lockreason, lockdate from syssrvroles')
2405  
2406          -- Resize syssrvroles.password if going to SMP version <= 15.0.1 or SDC version 15.0.1.5
2407          -- This step should be done after step 26 where syssrvroles.password column is accessed.
2408          insert #actions values (26, @masterdb, 15015,
2409              'delete statistics syssrvroles(password) ' +
2410              'update syscolumns set length = 30 ' +
2411              'where id = object_id(''syssrvroles'') and name = ''password''',
2412              'Downgrade : Resizing role password length.')
2413  
2414          -- Remove catalog changes for RSA Keypair Regeneration Period and Login Profile
2415          -- if going to <= SMP 15.5 or SDC 15.5.0.5
2416          insert #actions values (27, @masterdb, 15505,
2417              'delete sysattributes where class = 35 ' +
2418              'delete sysattributes where class = 39 ' +
2419              'update syslogins set lpid = null, crsuid = null ' +
2420              'where lpid is not null ' +
2421              'or crsuid is not null ' +
2422              'delete syscolumns where id = object_id(''syslogins'') ' +
2423              'and name in (''lpid'', ''crsuid'') ' +
2424              'delete syslogins where (status & @lp_status) = @lp_status ' +
2425              'update syslogins set status = status & ~(@exempt_lock) ' +
2426              'where (status & @exempt_lock) = @exempt_lock',
2427              'Downgrade : Removing catalog changes for RSA Keypair Regeneration Period and Login Profile')
2428  
2429          -- Remove login and login profile management audit options if going to <= SMP 15.5 or SDC 15.5.0.5
2430          if (@sybsecuritydb is not null)
2431          begin
2432              insert #actions values (28, @sybsecuritydb, 15505,
2433                  'delete from sybsecurity..sysauditoptions where ' +
2434                  'name in (''security_profile'', ''login_admin'')',
2435                  'Downgrade : Removing login and login profile management audit options')
2436          end
2437  
2438          -- Remove RAT configuration params if going to < 15.7
2439          insert #actions values (29, @masterdb, 15699,
2440              'delete from sysconfigures where config = 542',
2441              'Downgrade : Removing 15.7 RAT configuration parameters.')
2442  
2443          -- Remove RAT MRP sysattribute values if going to < 15.7
2444          insert #actions values (30, @allbutmasterdb, 15699,
2445              'delete from :DBNAME:.. sysattributes where ' +
2446              '(class = 11 and object_info1 > 0) ' +
2447              'or (class = 11 and attribute in (36, 37, 38, 39, 40, 41, 42, 43, 44, 45)) ' +
2448              'or (class = 41)',
2449              'Downgrade : Removing RAT MRP sysattribute rows')
2450  
2451          -- Remove monLockTimeout table configuration params if going to < 15.7
2452          insert #actions values (31, @masterdb, 15699,
2453              'delete from sysconfigures where config in (544, 545)',
2454              'Downgrade : Removing ''lock timeout pipe max messages'' and ''lock timeout pipe active'' configuration parameters.')
2455  
2456          -- Remove 15.7 config options if going to < 15.7
2457          insert #actions values (32, @masterdb, 15699,
2458              'delete from master..sysconfigures where (config in (45, 49, 50, 472, 503, 514)) ' +
2459              'or (config > 516)',
2460              'Downgrade : Removing configuration options added in 15.7')
2461  
2462          -- Remove 15.7 in-row LOB fields if going to < 15.7
2463          insert #actions values (33, @alldbs, 15699,
2464              'update :DBNAME:..syscolumns set lobcomp_lvl = null where lobcomp_lvl is not null ' +
2465              'update :DBNAME:..syscolumns set inrowlen = null where inrowlen is not null ' +
2466              'delete from :DBNAME:..syscolumns where id = object_id(''syscolumns'') ' +
2467              'and name in (''lobcomp_lvl'', ''inrowlen'')',
2468              'Downgrade : Removing syscolumns.lobcomp_lvl and syscolumns.inrowlen')
2469  
2470          -- Remove 15.7 in-row LOB fields if going to < 15.7
2471          insert #actions values (34, @masterdb, 15699,
2472              'update sysdatabases set lobcomp_lvl = null where lobcomp_lvl is not null ' +
2473              'update sysdatabases set inrowlen = null where inrowlen is not null ' +
2474              'update sysdatabases set dcompdefaultlevel = null where dcompdefaultlevel is not null ' +
2475              'delete from syscolumns where id = object_id(''sysdatabases'') ' +
2476              'and name in (''lobcomp_lvl'', ''inrowlen'', ''dcompdefaultlevel'')',
2477              'Downgrade : Removing sysdatabases.lobcomp_lvl and sysdatabases.inrowlen')
2478  
2479          -- Remove syscomments.version if going to < 15.7
2480          insert #actions values (35, @alldbs, 15699,
2481              'delete from :DBNAME:..syscolumns where id = 6 and name = ''version''',
2482              'Downgrade : Removing syscomments.version')
2483  
2484          -- Revert sysusages columns to 'int' if going to < 15.7. This batch
2485          -- changes column datatypes and patches index sysusages.csysusages
2486          insert #actions values (36, @masterdb, 15699,
2487              'update master.dbo.syscolumns set type=38, usertype=7 where id = 31 and name = ''unreservedpgs'' ' +
2488              'update master.dbo.syscolumns set type=56, usertype=7 where id = 31 and name in (''lstart'',''size'') ' +
2489              'select @c=set_index_key_type(1, 31, 3, 2, 56)',
2490              'Downgrade : Revert sysusages columns to ''int''')
2491  
2492          insert #actions values (37, @masterdb, 15699,
2493              'update master.dbo.syscolumns set type=56, usertype=7 where id=34 and usertype=45 and name=''page''',
2494              'Downgrade : Revert syslocks.page to ''int''')
2495  
2496          insert #actions values (38, @masterdb, 15699,
2497              'update master.dbo.syscolumns set type=56, usertype=7 where id=50 and usertype=45 and name=''page''',
2498              'Downgrade : Revert syslogshold.page to ''int''')
2499  
2500          insert #actions values (39, @masterdb, 15699,
2501              'update master.dbo.syscolumns set type=56, usertype=7 where id=89 and usertype=45 and ' +
2502              'name in (''lstart'',''size'')',
2503              'Downgrade : Revert sysaltusages columns to ''int''')
2504  
2505          insert #actions values (40, @alldbs, 15699,
2506              'update :DBNAME:.dbo.syscolumns set type=56, usertype=7 where id=18 and usertype=45 and name=''free_space''',
2507              'Downgrade : Revert systhresholds.free_space to ''int''')
2508  
2509          insert #actions values (41, @alldbs, 15699,
2510              'update :DBNAME:.dbo.syscolumns set type=56, usertype=7 where id=28 and usertype=45 and ' +
2511              'name in (''datoampage'',''indoampage'',''firstpage'',''rootpage'')',
2512              'Downgrade : Revert syspartitions columns to ''int''')
2513  
2514          insert #actions values (42, @alldbs, 15699,
2515              'update :DBNAME:.dbo.syscolumns set type=56, usertype=7 where id=23 and usertype=45 and ' +
2516              'name in (''leafcnt'',''pagecnt'',''emptypgcnt'',''warmcachepgcnt'',''unusedpgcnt'',''oampagecnt'')',
2517              'Downgrade : Revert systabstats columns to ''int''')
2518  
2519          insert #actions values (43, @masterdb, 15699,
2520              'delete master.dbo.sysconfigures where config in (564, 565)',
2521              'Downgrade: Remove dump history config options')
2522  
2523          insert #actions values (44, @alldbs, 15699,
2524              'delete :DBNAME:.dbo.sysprotects where predid is not NULL',
2525              'Downgrade: Delete all predicated privileges from sysprotects')
2526  
2527          insert #actions values (45, @masterdb, 15699,
2528              'delete master.dbo.sysloginroles where predid is not NULL',
2529              'Downgrade: Delete all role activation predicates from sysloginroles')
2530  
2531          insert #actions values (46, @alldbs, 15699,
2532              'delete :DBNAME:.dbo.syscomments from :DBNAME:.dbo.syscomments c, :DBNAME:.dbo.sysobjects o' +
2533              ' where o.type = ''RP'' and o.id=c.id',
2534              'Downgrade: Delete all predicates from syscomments')
2535  
2536          insert #actions values (47, @alldbs, 15699,
2537              'delete :DBNAME:.dbo.sysprocedures from :DBNAME:.dbo.sysprocedures p, :DBNAME:.dbo.sysobjects o' +
2538              ' where o.type = ''RP'' and o.id=p.id',
2539              'Downgrade: Delete all predicates from sysprocedures')
2540  
2541          insert #actions values (48, @alldbs, 15699,
2542              'delete :DBNAME:.dbo.sysdepends from :DBNAME:.dbo.sysdepends d, :DBNAME:.dbo.sysobjects o' +
2543              ' where o.type = ''RP'' and o.id=d.id',
2544              'Downgrade: Delete all predicates from sysdepends')
2545  
2546          insert #actions values (49, @alldbs, 15699,
2547              'delete :DBNAME:.dbo.sysobjects where type = ''RP''',
2548              'Downgrade: Delete all predicates from sysobjects')
2549  
2550          insert #actions values (50, @alldbs, 15699,
2551              'delete :DBNAME:.dbo.syscolumns where id=9 and name in (''predid'', ''status'')',
2552              'Downgrade: Delete new columns predid and status from sysprotects added for predicated privileges')
2553  
2554          insert #actions values (51, @masterdb, 15699,
2555              'delete :DBNAME:.dbo.syscolumns where id=49 and name=''predid''',
2556              'Downgrade: Delete new column predid from sysloginroles added for activation predicates')
2557  
2558          insert #actions values (52, @alldbs, 15699,
2559              'delete :DBNAME:.dbo.sysprotects from :DBNAME:.dbo.sysprotects p, :DBNAME:.dbo.sysusers u' +
2560              ' where p.grantor=u.uid and u.name=''usedb_user''',
2561              'Downgrade: Delete all permissions granted by usedb_user')
2562  
2563          insert #actions values (53, @alldbs, 15699,
2564              'delete :DBNAME:.dbo.sysusers where name=''usedb_user''',
2565              'Downgrade: Drop user usedb_user')
2566  
2567          insert #actions values (54, @alldbs, 15699,
2568              'delete :DBNAME:.dbo.sysprotects where action=5 and id = @authmech',
2569              'Downgrade: Delete new builtin permission for authmech')
2570  
2571          insert #actions values (55, @masterdb, 15699,
2572              'delete :DBNAME:.dbo.sysattributes where class=1 and attribute in (1648,1649)',
2573              'Downgrade: Delete upgrade items which add sa_serverprivs_role and grant system roles to logins')
2574  
2575          insert #actions values (56, @alldbs, 15699,
2576              'delete :DBNAME:.dbo.sysattributes where class=1 and attribute in (1650,1651,1652,1653)',
2577              'Downgrade: Delete upgrade items which add granular specific permissions and formats for permissions')
2578  
2579          insert #actions values (57, @masterdb, 15699,
2580              'drop role sa_serverprivs_role with override',
2581              'Downgrade: Drop user defined role sa_serverprivs_role')
2582  
2583          insert #actions values (58, @alldbs, 15699,
2584              'delete :DBNAME:.dbo.sysprotects from :DBNAME:.dbo.sysprotects p, master..spt_values v' +
2585              ' where p.action=v.number and v.type=''GP''',
2586              'Downgrade: Delete all new granular permissions from sysprotects')
2587  
2588          insert #actions values (59, @alldbs, 15699,
2589              'update :DBNAME:.dbo.syspartitions set status = status - 256 where status & 256 != 0',
2590              'Downgrade: Turn off partition level off-row column back linking indicators')
2591  
2592          insert #actions values (60, @masterdb, 15699,
2593              'update master.dbo.sysdatabases set status = status - 1 where status & 1 != 0',
2594              'Downgrade: Turn off database level off-row column back linking indicators')
2595  
2596          insert #actions values (61, @alldbs, 15699,
2597              'delete :DBNAME:.dbo.sysattributes where class=43 and attribute in (0, 1, 2)',
2598              'Downgrade: Removing HADR sysattributes rows')
2599  
2600          insert #actions values (62, @masterdb, 15699,
2601              'delete master.dbo.sysservers where srvclass in (16, 17)',
2602              'Downgrade: Removing HADR MEMBER and HADR GROUP rows from sysservers')
2603  
2604          insert #actions values (63, @masterdb, 15699,
2605              'delete master.dbo.sysconfigures where config=587',
2606              'Downgrade: Removing max utility parallel degree config option row from sysconfigures')
2607  
2608          insert #actions values (64, @masterdb, 15699,
2609              'delete master.dbo.sysconfigures where config = 458',
2610              'Downgrade: Remove recovery prefetch size option')
2611  
2612  
2613          /*
2614          ** MAINTAINERS:  add downgrade tasks in this area.
2615          **
2616          ** Place individual tasks above this comment, incrementing the
2617          ** action ID.
2618          **
2619          ** Place changes to upgrade IDs below this comment, decrementing
2620          ** the action ID.  These changes must be in _descending order_ by
2621          ** target version, since the last one processed will be the ultimate
2622          ** value retained.  These must be the highest-numbered items in the
2623          ** list, since we want them to execute last.  The minor ID and
2624          ** ASEvers together act as indicators showing that the database
2625          ** is exactly at the target downgrade version.
2626          */
2627          -- Reset minor upgrade ID as appropriate to the target version
2628          insert #actions values (989, @masterdb, 15699,
2629              @downgrade_sysdams,
2630              'Downgrade : Drop SYSDAMS')
2631          insert #actions values (990, @masterdb, 15699,
2632              @downgrade_dump_config,
2633              'Downgrade : Remove DUMP CONFIG configuration')
2634          insert #actions values (991, @masterdb, 15500,
2635              @dbinfo_update_1550_master_sql,
2636              'Downgrade : Setting master database minor upgrade version.')
2637          insert #actions values (992, @allbutmasterdb, 15500,
2638              @dbinfo_update_1550_userdb_sql,
2639              'Downgrade : Setting user databases minor upgrade version.')
2640          insert #actions values (993, @masterdb, 15020,
2641              @dbinfo_update_1502_master_sql,
2642              'Downgrade : Setting master database minor upgrade version.')
2643          insert #actions values (994, @allbutmasterdb, 15020,
2644              @dbinfo_update_1502_userdb_sql,
2645              'Downgrade : Setting user databases minor upgrade version.')
2646          insert #actions values (995, @alldbs, 15015,
2647              @dbinfo_update_15015_alldbs_sql,
2648              'Downgrade : Setting user databases minor upgrade version.')
2649          insert #actions values (996, @masterdb, 15010,
2650              @dbinfo_update_1500_master_sql,
2651              'Downgrade : Setting master database minor upgrade version.')
2652          insert #actions values (997, @allbutmasterdb, 15010,
2653              @dbinfo_update_1500_userdb_sql,
2654              'Downgrade : Setting user databases minor upgrade version.')
2655  
2656          -- Turn on 0x00000020 for dbi_status4 to indicate downgrade happened
2657          insert #actions values (998, @masterdb, @any15versid,
2658              @dbinfo_turnon_downgradeneed_sql,
2659              'Downgrade : Turning on database downgrade indicator.')
2660  
2661          -- Reset ASEvers to a value appropriate to the target version
2662          insert #actions values (999, @alldbs, @any15versid,
2663              @dbinfo_reset_asevers_sql,
2664              'Downgrade : Resetting database version indicator.')
2665  
2666          /*
2667          ** MAINTAINERS:  as you add tasks, add them above or below the
2668          ** "Reset minor upgrade ID" items, as appropriate.  Add no items
2669          ** beyond value 999.
2670          */
2671  
2672  
2673          /* A cursor to traverse sysdatabases */
2674          declare sysdbc cursor for
2675          select dbid from #sysdatabases
2676  
2677          /* 
2678          ** This cursor will traverse the #actions database and the
2679          ** actions will be performed based on the from and to versions:
2680          ** perform the action if [toversion is "any"] or [toversion
2681          ** less than current AND greater or equal to target].
2682          */
2683          declare actc cursor for
2684          select actionid, dbid, action, errorlog_msg
2685          from #actions
2686          where (toversion = @any15versid)
2687              or ((toversion < @fromversid) and (toversion >= @toversid))
2688          order by actionid
2689  
2690          dbcc printolog('Starting downgrading ASE.')
2691          select @exec_error_count = 0
2692  
2693          open actc
2694          fetch actc into @actionid, @whatdbid, @sqlbuf, @errorlog_msg
2695          while (@@sqlstatus = 0 and @exec_error_count = 0)
2696          begin
2697              if (@verbose = 1)
2698              begin
2699                  print ''
2700                  print 'Executing downgrade step %1! [%2!] for :',
2701                      @actionid, @sqlbuf
2702              end
2703  
2704              /* 
2705              ** Write related downgrade step informational message in 
2706              ** the errorlog.
2707              */
2708              dbcc printolog(@errorlog_msg)
2709  
2710              /* 
2711              ** Loop for each writable database and execute 
2712              ** the command if it is authorized. 
2713              */
2714              open sysdbc
2715              fetch sysdbc into @dbid
2716              while (@@sqlstatus = 0)
2717              begin -- {
2718                  if ((@whatdbid in (@alldbs, @dbid)) or
2719                          (@whatdbid = @allbutmasterdb and @dbid != @masterdb))
2720                  begin -- {
2721                      select @dbname = db_name(@dbid)
2722                      select @orig_dbname = @dbname
2723                      /* 
2724                      ** Database name in which there is tab or blank
2725                      ** must be delimited with '[]' in sql cmd. 
2726                      */
2727                      if ((patindex(@tab_str, @dbname) > 0)
2728                              or (patindex(@blank_str, @dbname) > 0))
2729                      begin
2730                          select @dbname = '[' + @orig_dbname + ']'
2731                      end
2732  
2733                      select @sqlcmd = @sqlbuf
2734                      /* 
2735                      ** First, replace the dbname in the selection stmts.
2736                      ** The context looks like 'dbname.dbo' or 'dbname..'
2737                      */
2738                      select @dbnameoffset = patindex('%:DBNAME:.%', @sqlcmd)
2739                      while (@dbnameoffset > 0)
2740                      begin
2741                          select @sqlcmd =
2742                              stuff(@sqlcmd,
2743                                  @dbnameoffset, 8,
2744                                  @dbname)
2745                          select @dbnameoffset = patindex('%:DBNAME:.%',
2746                                  @sqlcmd)
2747                      end
2748                      /* Then, replace the dbname in other situations. */
2749                      select @dbnameoffset = patindex('%:DBNAME:%', @sqlcmd)
2750                      while (@dbnameoffset > 0)
2751                      begin
2752                          select @sqlcmd =
2753                              stuff(@sqlcmd,
2754                                  @dbnameoffset, 8,
2755                                  @orig_dbname)
2756                          select @dbnameoffset = patindex('%:DBNAME:%',
2757                                  @sqlcmd)
2758  
2759                      end
2760                      if (@verbose = 1)
2761                      begin
2762                          print '- Database: %1! (dbid: %2!)',
2763                              @orig_dbname, @dbid
2764                          if (@show_cmd = 1)
2765                          begin
2766                              print 'sql command is: %1!', @sqlcmd
2767                              print ''
2768                          end
2769                      end
2770                      if (@durability_sql = @sqlcmd)
2771                      begin
2772                          /* 
2773                          ** sp_downgrade_durability cannot
2774                          ** be run in execute immediate.
2775                          */
2776                          exec sp_downgrade_durability 'exec'
2777                      end
2778                      else
2779                      if (@sysobjstat3_sql = @sqlcmd)
2780                      begin
2781                          /*
2782                          ** sp_downgrade_sysobjects cannot
2783                          ** be run in execute immediate.
2784                          */
2785                          exec sp_downgrade_sysobjects 'exec'
2786                      end
2787                      else
2788                      if (@downgrade_15015_config = @sqlcmd)
2789                      begin
2790                          if (@toversid = 15015)
2791                          begin
2792                              exec (@downgrade_sysconfigures_15015_sql1)
2793                              exec (@downgrade_sysconfigures_15015_sql2)
2794                              exec (@downgrade_sysconfigures_15015_sql3)
2795                              exec (@downgrade_sysconfigures_15015_cmd)
2796                          end
2797                      end
2798                      else
2799                      if (@downgrade_15015_msg = @sqlcmd)
2800                      begin
2801                          if (@toversid = 15015)
2802                          begin
2803                              set nocount on
2804                              exec sp_inst15015msg
2805                              set nocount off
2806                              update master..syslanguages set upgrade = 1501
2807                          end
2808                      end
2809                      else
2810                      if ((@reset_sysservers_srvnetname_len = @sqlcmd) and (@toversid = 15015))
2811                      begin
2812                          /*
2813                          ** Skip this downgrade item, as len of sysservers.srvnetname is
2814                          ** still 255 in 15.0.1.5.
2815                          */
2816                          if (@verbose = 1)
2817                          begin
2818                              print ''
2819                              print "  Skip downgrade step %1! for database %2! on downgrading to version %3!",
2820                                  @actionid, @orig_dbname, @toversid
2821                          end
2822                      end
2823                      else
2824                      begin
2825                          exec (@sqlcmd)
2826                          if (@@error != 0)
2827                          begin
2828                              select @exec_error_count = @exec_error_count + 1
2829                              print 'downgrade step %1! failed.', @actionid
2830                              print 'failing command is: %1!', @sqlcmd
2831                          end
2832                      end
2833                  end -- }
2834                  fetch sysdbc into @dbid
2835              end -- }
2836              close sysdbc
2837  
2838              /* Pick up next downgrade step. */
2839              fetch actc into @actionid, @whatdbid, @sqlbuf, @errorlog_msg
2840          end
2841          close actc
2842  
2843          deallocate cursor actc
2844          deallocate cursor sysdbc
2845  
2846          if (@toversid < 15700)
2847          begin --{		
2848  
2849              /* A cursor to traverse sysdatabases */
2850              declare sysdbc1 cursor for
2851              select dbid from #sysdatabases
2852  
2853              open sysdbc1
2854              fetch sysdbc1 into @dbid
2855  
2856              while (@@sqlstatus = 0)
2857              begin -- {
2858                  select @dbname = db_name(@dbid)
2859                  select @orig_dbname = @dbname
2860                  /* 
2861                  ** Database name in which there is tab or blank
2862                  ** must be delimited with '[]' in sql cmd. 
2863                  */
2864                  if ((patindex(@tab_str, @dbname) > 0)
2865                          or (patindex(@blank_str, @dbname) > 0))
2866                  begin
2867                      select @dbname = '[' + @orig_dbname + ']'
2868                  end
2869  
2870                  /*
2871                  ** Drop sysprotects.csysprotects index as predid was 
2872                  ** added to index for predicated privileges 
2873                  */
2874                  print ''
2875                  print 'Dropping and recreating index on Sysprotects for database %1!',
2876                      @orig_dbname
2877  
2878                  dbcc dbrepair(@orig_dbname, REPAIRINDEX, 'sysprotects', 2,
2879                      @downgrd_sysprot_index)
2880  
2881                  fetch sysdbc1 into @dbid
2882              end --}
2883          end --}
2884  
2885          close sysdbc1
2886          deallocate cursor sysdbc1
2887  
2888          if @exec_error_count != 0
2889          begin
2890              dbcc printolog('One or more downgrade steps failed. Downgrade is not complete.')
2891              return 1
2892          end
2893  
2894          dbcc printolog('ASE downgrade completed.')
2895          return 0
2896      end -- }
2897  


exec sp_procxmode 'sp_downgrade', 'AnyMode'
go

Grant Execute on sp_downgrade to public
go
RESULT SETS
sp_downgrade_rset_007
sp_downgrade_rset_006
sp_downgrade_rset_005
sp_downgrade_rset_004
sp_downgrade_rset_003
sp_downgrade_rset_002
sp_downgrade_rset_001
sp_downgrade_rset_008

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 516
 MEST 4 Empty String will be replaced by Single Space 617
 MEST 4 Empty String will be replaced by Single Space 634
 MEST 4 Empty String will be replaced by Single Space 636
 MEST 4 Empty String will be replaced by Single Space 820
 MEST 4 Empty String will be replaced by Single Space 849
 MEST 4 Empty String will be replaced by Single Space 1459
 MEST 4 Empty String will be replaced by Single Space 1466
 MEST 4 Empty String will be replaced by Single Space 1468
 MEST 4 Empty String will be replaced by Single Space 1503
 MEST 4 Empty String will be replaced by Single Space 1505
 MEST 4 Empty String will be replaced by Single Space 1606
 MEST 4 Empty String will be replaced by Single Space 1625
 MEST 4 Empty String will be replaced by Single Space 1628
 MEST 4 Empty String will be replaced by Single Space 1670
 MEST 4 Empty String will be replaced by Single Space 1692
 MEST 4 Empty String will be replaced by Single Space 1713
 MEST 4 Empty String will be replaced by Single Space 1846
 MEST 4 Empty String will be replaced by Single Space 1848
 MEST 4 Empty String will be replaced by Single Space 1899
 MEST 4 Empty String will be replaced by Single Space 1901
 MEST 4 Empty String will be replaced by Single Space 1921
 MEST 4 Empty String will be replaced by Single Space 1923
 MEST 4 Empty String will be replaced by Single Space 1925
 MEST 4 Empty String will be replaced by Single Space 2699
 MEST 4 Empty String will be replaced by Single Space 2767
 MEST 4 Empty String will be replaced by Single Space 2818
 MEST 4 Empty String will be replaced by Single Space 2874
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MINU 4 Unique Index with nullable columns master..syslanguages master..syslanguages
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 615
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 1630
 QCSC 4 Costly 'select count()', use 'exists()' 1589
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent}
963
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent}
969
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
1004
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
1051
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
1055
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
1114
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 356
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 366
 QTYP 4 Comparison type mismatch smallint = int 366
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 458
 QTYP 4 Comparison type mismatch smallint = int 458
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 499
 QTYP 4 Comparison type mismatch smallint = int 499
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 817
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 837
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 841
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 845
 QTYP 4 Comparison type mismatch tinyint = int 845
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 963
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 969
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1085
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1089
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1100
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1104
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1226
 QTYP 4 Comparison type mismatch smallint = int 1226
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1431
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1596
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1598
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1600
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1612
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1613
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 1622
 QUDW 4 Update or delete with no where clause 2806
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 TNOI 4 Table with no index master..syslisteners master..syslisteners
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 VRUN 4 Variable is read and not initialized @pgcount 1257
 VRUN 4 Variable is read and not initialized @maxlen 1830
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc2 540
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc1 726
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc1 770
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc0 889
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdevc 932
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause usercachec 968
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc0 1195
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc0 1796
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc0 1861
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc1 2059
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc1 2851
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 396
 MAW1 3 Warning message on %name% master..syscharsets.id: Warning message on syscharsets 845
 MAW1 3 Warning message on %name% tempdb..#perm.id: Warning message on #perm_crby_sybsystemprocs__sp_downgrade 1605
 MAW1 3 Warning message on %name% tempdb..#perm.id: Warning message on #perm_crby_sybsystemprocs__sp_downgrade 1607
 MAW1 3 Warning message on %name% tempdb..#perm.id: Warning message on #perm_crby_sybsystemprocs__sp_downgrade 1610
 MAW1 3 Warning message on %name% tempdb..#perm.id: Warning message on #perm_crby_sybsystemprocs__sp_downgrade 1616
 MAW1 3 Warning message on %name% tempdb..#tables.id: Warning message on #tables_crby_sybsystemprocs__sp_downgrade 1616
 MAW1 3 Warning message on %name% tempdb..#perm.id: Warning message on #perm_crby_sybsystemprocs__sp_downgrade 1617
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 21
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..syscharsets  
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public master..syslanguages  
 MGTP 3 Grant to public master..syslisteners  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_downgrade  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 155
 MNER 3 No Error Check should check @@error after select into 307
 MNER 3 No Error Check should check @@error after select into 316
 MNER 3 No Error Check should check return value of exec 404
 MNER 3 No Error Check should check return value of exec 408
 MNER 3 No Error Check should check return value of exec 513
 MNER 3 No Error Check should check @@error after select into 546
 MNER 3 No Error Check should check return value of exec 615
 MNER 3 No Error Check should check @@error after select into 667
 MNER 3 No Error Check should check return value of exec 673
 MNER 3 No Error Check should check @@error after select into 698
 MNER 3 No Error Check should check return value of exec 704
 MNER 3 No Error Check should check @@error after update 1018
 MNER 3 No Error Check should check return value of exec 1212
 MNER 3 No Error Check should check return value of exec 1218
 MNER 3 No Error Check should check @@error after select into 1592
 MNER 3 No Error Check should check return value of exec 1630
 MNER 3 No Error Check should check @@error after delete 1637
 MNER 3 No Error Check should check @@error after delete 1638
 MNER 3 No Error Check should check @@error after delete 1639
 MNER 3 No Error Check should check @@error after delete 1640
 MNER 3 No Error Check should check @@error after delete 2171
 MNER 3 No Error Check should check @@error after delete 2172
 MNER 3 No Error Check should check @@error after insert 2197
 MNER 3 No Error Check should check @@error after insert 2202
 MNER 3 No Error Check should check @@error after insert 2207
 MNER 3 No Error Check should check @@error after insert 2212
 MNER 3 No Error Check should check @@error after insert 2240
 MNER 3 No Error Check should check @@error after insert 2244
 MNER 3 No Error Check should check @@error after insert 2248
 MNER 3 No Error Check should check @@error after insert 2253
 MNER 3 No Error Check should check @@error after insert 2263
 MNER 3 No Error Check should check @@error after insert 2283
 MNER 3 No Error Check should check @@error after insert 2288
 MNER 3 No Error Check should check @@error after insert 2292
 MNER 3 No Error Check should check @@error after insert 2297
 MNER 3 No Error Check should check @@error after insert 2303
 MNER 3 No Error Check should check @@error after insert 2311
 MNER 3 No Error Check should check @@error after insert 2321
 MNER 3 No Error Check should check @@error after insert 2327
 MNER 3 No Error Check should check @@error after insert 2332
 MNER 3 No Error Check should check @@error after insert 2345
 MNER 3 No Error Check should check @@error after insert 2350
 MNER 3 No Error Check should check @@error after insert 2356
 MNER 3 No Error Check should check @@error after insert 2361
 MNER 3 No Error Check should check @@error after insert 2366
 MNER 3 No Error Check should check @@error after insert 2377
 MNER 3 No Error Check should check @@error after insert 2388
 MNER 3 No Error Check should check @@error after insert 2408
 MNER 3 No Error Check should check @@error after insert 2416
 MNER 3 No Error Check should check @@error after insert 2432
 MNER 3 No Error Check should check @@error after insert 2439
 MNER 3 No Error Check should check @@error after insert 2444
 MNER 3 No Error Check should check @@error after insert 2452
 MNER 3 No Error Check should check @@error after insert 2457
 MNER 3 No Error Check should check @@error after insert 2463
 MNER 3 No Error Check should check @@error after insert 2471
 MNER 3 No Error Check should check @@error after insert 2480
 MNER 3 No Error Check should check @@error after insert 2486
 MNER 3 No Error Check should check @@error after insert 2492
 MNER 3 No Error Check should check @@error after insert 2496
 MNER 3 No Error Check should check @@error after insert 2500
 MNER 3 No Error Check should check @@error after insert 2505
 MNER 3 No Error Check should check @@error after insert 2509
 MNER 3 No Error Check should check @@error after insert 2514
 MNER 3 No Error Check should check @@error after insert 2519
 MNER 3 No Error Check should check @@error after insert 2523
 MNER 3 No Error Check should check @@error after insert 2527
 MNER 3 No Error Check should check @@error after insert 2531
 MNER 3 No Error Check should check @@error after insert 2536
 MNER 3 No Error Check should check @@error after insert 2541
 MNER 3 No Error Check should check @@error after insert 2546
 MNER 3 No Error Check should check @@error after insert 2550
 MNER 3 No Error Check should check @@error after insert 2554
 MNER 3 No Error Check should check @@error after insert 2558
 MNER 3 No Error Check should check @@error after insert 2563
 MNER 3 No Error Check should check @@error after insert 2567
 MNER 3 No Error Check should check @@error after insert 2571
 MNER 3 No Error Check should check @@error after insert 2575
 MNER 3 No Error Check should check @@error after insert 2579
 MNER 3 No Error Check should check @@error after insert 2583
 MNER 3 No Error Check should check @@error after insert 2588
 MNER 3 No Error Check should check @@error after insert 2592
 MNER 3 No Error Check should check @@error after insert 2596
 MNER 3 No Error Check should check @@error after insert 2600
 MNER 3 No Error Check should check @@error after insert 2604
 MNER 3 No Error Check should check @@error after insert 2608
 MNER 3 No Error Check should check @@error after insert 2628
 MNER 3 No Error Check should check @@error after insert 2631
 MNER 3 No Error Check should check @@error after insert 2634
 MNER 3 No Error Check should check @@error after insert 2637
 MNER 3 No Error Check should check @@error after insert 2640
 MNER 3 No Error Check should check @@error after insert 2643
 MNER 3 No Error Check should check @@error after insert 2646
 MNER 3 No Error Check should check @@error after insert 2649
 MNER 3 No Error Check should check @@error after insert 2652
 MNER 3 No Error Check should check @@error after insert 2657
 MNER 3 No Error Check should check @@error after insert 2662
 MNER 3 No Error Check should check return value of exec 2776
 MNER 3 No Error Check should check return value of exec 2785
 MNER 3 No Error Check should check return value of exec 2804
 MNER 3 No Error Check should check @@error after update 2806
 MUCO 3 Useless Code Useless Begin-End Pair 28
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 236
 MUCO 3 Useless Code Useless Brackets 237
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 259
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 280
 MUCO 3 Useless Code Useless Brackets 322
 MUCO 3 Useless Code Useless Brackets 328
 MUCO 3 Useless Code Useless Brackets 341
 MUCO 3 Useless Code Useless Brackets 350
 MUCO 3 Useless Code Useless Brackets 395
 MUCO 3 Useless Code Useless Brackets 402
 MUCO 3 Useless Code Useless Brackets 406
 MUCO 3 Useless Code Useless Brackets 410
 MUCO 3 Useless Code Useless Brackets 427
 MUCO 3 Useless Code Useless Brackets 437
 MUCO 3 Useless Code Useless Brackets 454
 MUCO 3 Useless Code Useless Brackets 465
 MUCO 3 Useless Code Useless Brackets 473
 MUCO 3 Useless Code Useless Brackets 485
 MUCO 3 Useless Code Useless Brackets 566
 MUCO 3 Useless Code Useless Brackets 576
 MUCO 3 Useless Code Useless Brackets 588
 MUCO 3 Useless Code Useless Brackets 593
 MUCO 3 Useless Code Useless Brackets 601
 MUCO 3 Useless Code Useless Brackets 625
 MUCO 3 Useless Code Useless Brackets 638
 MUCO 3 Useless Code Useless Brackets 651
 MUCO 3 Useless Code Useless Brackets 731
 MUCO 3 Useless Code Useless Brackets 740
 MUCO 3 Useless Code Useless Brackets 775
 MUCO 3 Useless Code Useless Brackets 784
 MUCO 3 Useless Code Useless Brackets 818
 MUCO 3 Useless Code Useless Brackets 833
 MUCO 3 Useless Code Useless Brackets 860
 MUCO 3 Useless Code Useless Brackets 874
 MUCO 3 Useless Code Useless Brackets 895
 MUCO 3 Useless Code Useless Brackets 898
 MUCO 3 Useless Code Useless Brackets 902
 MUCO 3 Useless Code Useless Brackets 938
 MUCO 3 Useless Code Useless Brackets 974
 MUCO 3 Useless Code Useless Brackets 1000
 MUCO 3 Useless Code Useless Brackets 1005
 MUCO 3 Useless Code Useless Brackets 1036
 MUCO 3 Useless Code Useless Brackets 1047
 MUCO 3 Useless Code Useless Brackets 1057
 MUCO 3 Useless Code Useless Brackets 1076
 MUCO 3 Useless Code Useless Brackets 1092
 MUCO 3 Useless Code Useless Brackets 1107
 MUCO 3 Useless Code Useless Brackets 1115
 MUCO 3 Useless Code Useless Brackets 1134
 MUCO 3 Useless Code Useless Brackets 1160
 MUCO 3 Useless Code Useless Brackets 1202
 MUCO 3 Useless Code Useless Brackets 1204
 MUCO 3 Useless Code Useless Brackets 1216
 MUCO 3 Useless Code Useless Brackets 1237
 MUCO 3 Useless Code Useless Brackets 1247
 MUCO 3 Useless Code Useless Brackets 1257
 MUCO 3 Useless Code Useless Brackets 1267
 MUCO 3 Useless Code Useless Brackets 1276
 MUCO 3 Useless Code Useless Brackets 1295
 MUCO 3 Useless Code Useless Brackets 1326
 MUCO 3 Useless Code Useless Brackets 1346
 MUCO 3 Useless Code Useless Brackets 1371
 MUCO 3 Useless Code Useless Brackets 1392
 MUCO 3 Useless Code Useless Brackets 1409
 MUCO 3 Useless Code Useless Brackets 1431
 MUCO 3 Useless Code Useless Brackets 1441
 MUCO 3 Useless Code Useless Brackets 1483
 MUCO 3 Useless Code Useless Brackets 1525
 MUCO 3 Useless Code Useless Brackets 1568
 MUCO 3 Useless Code Useless Brackets 1589
 MUCO 3 Useless Code Useless Brackets 1607
 MUCO 3 Useless Code Useless Brackets 1612
 MUCO 3 Useless Code Useless Brackets 1650
 MUCO 3 Useless Code Useless Brackets 1703
 MUCO 3 Useless Code Useless Brackets 1707
 MUCO 3 Useless Code Useless Brackets 1725
 MUCO 3 Useless Code Useless Brackets 1730
 MUCO 3 Useless Code Useless Brackets 1740
 MUCO 3 Useless Code Useless Brackets 1751
 MUCO 3 Useless Code Useless Brackets 1763
 MUCO 3 Useless Code Useless Brackets 1793
 MUCO 3 Useless Code Useless Brackets 1804
 MUCO 3 Useless Code Useless Brackets 1811
 MUCO 3 Useless Code Useless Brackets 1817
 MUCO 3 Useless Code Useless Brackets 1866
 MUCO 3 Useless Code Useless Brackets 1873
 MUCO 3 Useless Code Useless Brackets 1883
 MUCO 3 Useless Code Useless Brackets 1914
 MUCO 3 Useless Code Useless Brackets 1916
 MUCO 3 Useless Code Useless Brackets 1938
 MUCO 3 Useless Code Useless Brackets 2040
 MUCO 3 Useless Code Useless Brackets 2063
 MUCO 3 Useless Code Useless Brackets 2071
 MUCO 3 Useless Code Useless Brackets 2077
 MUCO 3 Useless Code Useless Brackets 2164
 MUCO 3 Useless Code Useless Brackets 2309
 MUCO 3 Useless Code Useless Brackets 2319
 MUCO 3 Useless Code Useless Brackets 2430
 MUCO 3 Useless Code Useless Brackets 2695
 MUCO 3 Useless Code Useless Brackets 2697
 MUCO 3 Useless Code Useless Brackets 2716
 MUCO 3 Useless Code Useless Brackets 2718
 MUCO 3 Useless Code Useless Brackets 2727
 MUCO 3 Useless Code Useless Brackets 2739
 MUCO 3 Useless Code Useless Brackets 2750
 MUCO 3 Useless Code Useless Brackets 2760
 MUCO 3 Useless Code Useless Brackets 2764
 MUCO 3 Useless Code Useless Brackets 2770
 MUCO 3 Useless Code Useless Brackets 2779
 MUCO 3 Useless Code Useless Brackets 2788
 MUCO 3 Useless Code Useless Brackets 2790
 MUCO 3 Useless Code Useless Brackets 2799
 MUCO 3 Useless Code Useless Brackets 2801
 MUCO 3 Useless Code Useless Brackets 2810
 MUCO 3 Useless Code Useless Brackets 2816
 MUCO 3 Useless Code Useless Brackets 2826
 MUCO 3 Useless Code Useless Brackets 2846
 MUCO 3 Useless Code Useless Brackets 2856
 MUCO 3 Useless Code Useless Brackets 2864
 MUIN 3 Column created using implicit nullability 445
 MUIN 3 Column created using implicit nullability 1170
 MUIN 3 Column created using implicit nullability 1176
 MUIN 3 Column created using implicit nullability 1182
 MUIN 3 Column created using implicit nullability 1189
 MUIN 3 Column created using implicit nullability 2187
 MUOT 3 Updates outside transaction 2806
 QAFM 3 Var Assignment from potentially many rows 304
 QAFM 3 Var Assignment from potentially many rows 815
 QAFM 3 Var Assignment from potentially many rows 835
 QAFM 3 Var Assignment from potentially many rows 839
 QAFM 3 Var Assignment from potentially many rows 1002
 QAFM 3 Var Assignment from potentially many rows 1049
 QAFM 3 Var Assignment from potentially many rows 1053
 QAFM 3 Var Assignment from potentially many rows 1112
 QCRS 3 Conditional Result Set 338
 QCRS 3 Conditional Result Set 1031
 QCRS 3 Conditional Result Set 1071
 QCRS 3 Conditional Result Set 1088
 QCRS 3 Conditional Result Set 1103
 QCRS 3 Conditional Result Set 1129
 QCRS 3 Conditional Result Set 1732
 QCRS 3 Conditional Result Set 1776
 QCTC 3 Conditional Table Creation 445
 QCTC 3 Conditional Table Creation 546
 QCTC 3 Conditional Table Creation 667
 QCTC 3 Conditional Table Creation 698
 QCTC 3 Conditional Table Creation 1170
 QCTC 3 Conditional Table Creation 1176
 QCTC 3 Conditional Table Creation 1182
 QCTC 3 Conditional Table Creation 1189
 QCTC 3 Conditional Table Creation 1592
 QCTC 3 Conditional Table Creation 2046
 QCTC 3 Conditional Table Creation 2054
 QDIS 3 Check correct use of 'select distinct' 1592
 QGWO 3 Group by/Distinct/Union without order by 1088
 QGWO 3 Group by/Distinct/Union without order by 1103
 QGWO 3 Group by/Distinct/Union without order by 1592
 QGWO 3 Group by/Distinct/Union without order by 1776
 QJWT 3 Join or Sarg Without Index on temp table 1616
 QJWT 3 Join or Sarg Without Index on temp table 1623
 QNAJ 3 Not using ANSI Inner Join 1620
 QPNC 3 No column in condition 549
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
396
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object}
1226
 QSWV 3 Sarg with variable @dbid, Candidate Index: sysdatabases.ncsysdatabases unique(dbid) F 366
 QSWV 3 Sarg with variable @dbid, Candidate Index: sysdatabases.ncsysdatabases unique(dbid) F 499
 QTLO 3 Top-Level OR 663
 QTLO 3 Top-Level OR 670
 QTLO 3 Top-Level OR 694
 QTLO 3 Top-Level OR 701
 QTLO 3 Top-Level OR 1020
 QTLO 3 Top-Level OR 2686
 VNRD 3 Variable is not read @status1 155
 VNRD 3 Variable is not read @lp_status 180
 VNRD 3 Variable is not read @exempt_lock 181
 VNRD 3 Variable is not read @lockrole 182
 VUNU 3 Variable is not used @damstat 48
 VUNU 3 Variable is not used @objname 91
 VUNU 3 Variable is not used @objstat3 92
 VUNU 3 Variable is not used @dummy 107
 CRDO 2 Read Only Cursor Marker (has subqueries) 1195
 CRDO 2 Read Only Cursor Marker (has subqueries) 1796
 CRDO 2 Read Only Cursor Marker (has subqueries) 1861
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 2684
 CUPD 2 Updatable Cursor Marker (updatable by default) 540
 CUPD 2 Updatable Cursor Marker (updatable by default) 726
 CUPD 2 Updatable Cursor Marker (updatable by default) 770
 CUPD 2 Updatable Cursor Marker (updatable by default) 889
 CUPD 2 Updatable Cursor Marker (updatable by default) 932
 CUPD 2 Updatable Cursor Marker (updatable by default) 968
 CUPD 2 Updatable Cursor Marker (updatable by default) 2059
 CUPD 2 Updatable Cursor Marker (updatable by default) 2675
 CUPD 2 Updatable Cursor Marker (updatable by default) 2851
 MDYE 2 Dynamic Exec Marker exec @sqlcmd 1712
 MDYS 2 Dynamic SQL Marker 482
 MDYS 2 Dynamic SQL Marker 586
 MDYS 2 Dynamic SQL Marker 613
 MDYS 2 Dynamic SQL Marker 749
 MDYS 2 Dynamic SQL Marker 800
 MDYS 2 Dynamic SQL Marker 1246
 MDYS 2 Dynamic SQL Marker 1256
 MDYS 2 Dynamic SQL Marker 1266
 MDYS 2 Dynamic SQL Marker 1275
 MDYS 2 Dynamic SQL Marker 1294
 MDYS 2 Dynamic SQL Marker 1300
 MDYS 2 Dynamic SQL Marker 1306
 MDYS 2 Dynamic SQL Marker 1325
 MDYS 2 Dynamic SQL Marker 1331
 MDYS 2 Dynamic SQL Marker 1337
 MDYS 2 Dynamic SQL Marker 1345
 MDYS 2 Dynamic SQL Marker 1370
 MDYS 2 Dynamic SQL Marker 1375
 MDYS 2 Dynamic SQL Marker 1380
 MDYS 2 Dynamic SQL Marker 1391
 MDYS 2 Dynamic SQL Marker 1396
 MDYS 2 Dynamic SQL Marker 1401
 MDYS 2 Dynamic SQL Marker 1408
 MDYS 2 Dynamic SQL Marker 1414
 MDYS 2 Dynamic SQL Marker 1419
 MDYS 2 Dynamic SQL Marker 1450
 MDYS 2 Dynamic SQL Marker 1460
 MDYS 2 Dynamic SQL Marker 1492
 MDYS 2 Dynamic SQL Marker 1504
 MDYS 2 Dynamic SQL Marker 1534
 MDYS 2 Dynamic SQL Marker 1540
 MDYS 2 Dynamic SQL Marker 1552
 MDYS 2 Dynamic SQL Marker 1566
 MDYS 2 Dynamic SQL Marker 1586
 MDYS 2 Dynamic SQL Marker 1660
 MDYS 2 Dynamic SQL Marker 1669
 MDYS 2 Dynamic SQL Marker 1682
 MDYS 2 Dynamic SQL Marker 1691
 MDYS 2 Dynamic SQL Marker 1702
 MDYS 2 Dynamic SQL Marker 1828
 MDYS 2 Dynamic SQL Marker 1836
 MDYS 2 Dynamic SQL Marker 1847
 MDYS 2 Dynamic SQL Marker 1882
 MDYS 2 Dynamic SQL Marker 1892
 MDYS 2 Dynamic SQL Marker 1900
 MDYS 2 Dynamic SQL Marker 1913
 MDYS 2 Dynamic SQL Marker 2088
 MDYS 2 Dynamic SQL Marker 2103
 MDYS 2 Dynamic SQL Marker 2118
 MDYS 2 Dynamic SQL Marker 2126
 MDYS 2 Dynamic SQL Marker 2135
 MDYS 2 Dynamic SQL Marker 2792
 MDYS 2 Dynamic SQL Marker 2793
 MDYS 2 Dynamic SQL Marker 2794
 MDYS 2 Dynamic SQL Marker 2795
 MDYS 2 Dynamic SQL Marker 2825
 MRST 2 Result Set Marker 338
 MRST 2 Result Set Marker 1031
 MRST 2 Result Set Marker 1071
 MRST 2 Result Set Marker 1088
 MRST 2 Result Set Marker 1103
 MRST 2 Result Set Marker 1129
 MRST 2 Result Set Marker 1732
 MRST 2 Result Set Marker 1776
 MSUB 2 Subquery Marker 356
 MSUB 2 Subquery Marker 395
 MSUB 2 Subquery Marker 661
 MSUB 2 Subquery Marker 692
 MSUB 2 Subquery Marker 1026
 MSUB 2 Subquery Marker 1066
 MSUB 2 Subquery Marker 1082
 MSUB 2 Subquery Marker 1097
 MSUB 2 Subquery Marker 1124
 MSUB 2 Subquery Marker 1225
 MSUB 2 Subquery Marker 1431
 MSUB 2 Subquery Marker 1767
 MSUC 2 Correlated Subquery Marker 319
 MSUC 2 Correlated Subquery Marker 500
 MSUC 2 Correlated Subquery Marker 1196
 MSUC 2 Correlated Subquery Marker 1608
 MSUC 2 Correlated Subquery Marker 1614
 MSUC 2 Correlated Subquery Marker 1798
 MSUC 2 Correlated Subquery Marker 1862
 MTR1 2 Metrics: Comments Ratio Comments: 22% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 208 = 222dec - 16exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 1265 21

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysattributes (1)  
writes table tempdb..#grantee (1) 
reads table master..sysdevices (1)  
writes table sybsystemprocs..sp_downgrade_rset_006 
read_writes table tempdb..#users (1) 
calls proc sybsystemprocs..sp_encryption  
   calls proc sybsystemprocs..sp_aux_checkroleperm  
      reads table master..sysconfigures (1)  
      reads table master..syscurconfigs (1)  
   reads table master..sysdatabases (1)  
   calls proc sybsystemprocs..sp_getmessage  
      reads table sybsystemprocs..sysusermessages  
      reads table master..syslanguages (1)  
      reads table master..sysmessages (1)  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_autoformat  
      calls proc sybsystemprocs..sp_autoformat  
      writes table sybsystemprocs..sp_autoformat_rset_001 
      writes table sybsystemprocs..sp_autoformat_rset_003 
      reads table master..systypes (1)  
      read_writes table tempdb..#colinfo_af (1) 
      calls proc sybsystemprocs..sp_namecrack  
      writes table sybsystemprocs..sp_autoformat_rset_002 
      reads table tempdb..systypes (1)  
      reads table master..syscolumns (1)  
      reads table tempdb..syscolumns (1)  
      writes table sybsystemprocs..sp_autoformat_rset_004 
      writes table sybsystemprocs..sp_autoformat_rset_005 
   reads table sybsystemprocs..sysencryptkeys  
   read_writes table tempdb..#encrypted_column_info (1) 
   reads table sybsystemprocs..sysusers  
   writes table tempdb..#encrypted_extpasswd_info (1) 
   read_writes table tempdb..#encr_basekeys_info (1) 
   read_writes table tempdb..#encryption_keys_info (1) 
   read_writes table tempdb..#encr_column_info (1) 
   writes table tempdb..#extpasswd_status_info (1) 
   writes table tempdb..#encrypted_table_verify (1) 
   read_writes table tempdb..#keydbname_table (1) 
   writes table tempdb..#encrypted_text_info (1) 
   read_writes table tempdb..#encr_column_count (1) 
   read_writes table tempdb..#encr_keys_info (1) 
   reads table sybsystemprocs..sysattributes  
   read_writes table tempdb..#encrypted_verify_results (1) 
   reads table sybsystemprocs..syscomments  
   reads table sybsystemprocs..sysobjects  
   reads table master..sysattributes (1)  
   read_writes table tempdb..#encr_display_keys_info (1) 
   read_writes table tempdb..#encr_loginpwdcheck_info (1) 
   read_writes table tempdb..#encrypted_table_info (1) 
   read_writes table tempdb..#sys_encr_passwd_info (1) 
   read_writes table tempdb..#encrypted_database_info (1) 
   reads table master..syslogins (1)  
   read_writes table tempdb..#encr_keycopies_info (1) 
   reads table sybsystemprocs..syscolumns  
reads table master..sysservers (1)  
writes table sybsystemprocs..sp_downgrade_rset_008 
reads table master..syscharsets (1)  
calls proc sybsystemprocs..sp_passwordpolicy  
   calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syslogins (1)  
   reads table master..sysmessages (1)  
   reads table master..sysattributes (1)  
   calls proc sybsystemprocs..sp_getmessage  
   reads table master..syssrvroles (1)  
   reads table master..spt_values (1)  
   writes table tempdb..#helpdisplay (1) 
   writes table sybsystemprocs..sp_passwordpolicy_rset_003 
   reads table sybsystemprocs..sysattributes  
   writes table sybsystemprocs..sp_passwordpolicy_rset_002 
   calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_passwordpolicy_rset_001 
   writes table sybsystemprocs..sp_passwordpolicy_rset_004 
   calls proc sybsystemprocs..sp_ha_check_certified  
      reads table tempdb..sysobjects (1)  
writes table sybsystemprocs..sp_downgrade_rset_004 
read_writes table tempdb..#perm_dbcc_t_tab (1) 
read_writes table tempdb..#tables (1) 
calls proc sybsystemprocs..sp_downgrade_sysobjects  
   read_writes table tempdb..#sysdatabases1 (1) 
   read_writes table tempdb..#stat3obj (1) 
   reads table master..sysdatabases (1)  
writes table tempdb..#newformat (1) 
calls proc sybsystemprocs..sp_getmessage  
read_writes table tempdb..#perm (1) 
writes table tempdb..#newformat1 (1) 
reads table master..sysusages (1)  
read_writes table tempdb..#sysdatabases (1) 
read_writes table tempdb..#baddb (1) 
calls proc sybsystemprocs..sp_downgrade_durability  
writes table sybsystemprocs..sp_downgrade_rset_007 
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_autoformat  
writes table tempdb..#sprocDNR (1) 
read_writes table tempdb..#objects (1) 
read_writes table tempdb..#actions (1) 
writes table sybsystemprocs..sp_downgrade_rset_005 
reads table master..sysconfigures (1)  
reads table master..spt_values (1)  
reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_downgrade_sysdams  
   reads table master..spt_values (1)  
   reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_inst15015msg  
   calls proc sybsystemprocs..sp_inst15015msg_config  
   calls proc sybsystemprocs..sp_inst15015msg_all_oth_mesgs  
      calls proc sybsystemprocs..sp_inst15015msg_oth_mesg  
         reads table master..syslanguages (1)  
         calls proc sybsystemprocs..sp_inst15015msg_addlimsg  
            read_writes table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_inst15015msg_all_us_mesgs  
      calls proc sybsystemprocs..sp_inst15015msg_us_mesg  
         read_writes table master..sysmessages (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
reads table master..syslisteners (1)  
writes table sybsystemprocs..sp_downgrade_rset_003 
writes table master..syslanguages (1)  
writes table sybsystemprocs..sp_downgrade_rset_002 
read_writes table tempdb..#perm_bi_t_tab (1) 
reads table sybsystemprocs..syscolumns  
writes table sybsystemprocs..sp_downgrade_rset_001 
read_writes table master..sysdatabases (1)  
writes table tempdb..#permissions (1)