DatabaseProcApplicationCreatedLinks
sybsystemprocssp_downgrade_esd  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** SP_DOWNGRADE_ESD
4     **
5     ** Description
6     **	This stored procedure makes a database suitable for use by an indicated
7     **	SP of ASE 15.7. It is used both for making dumps compatible for
8     **	loading and for downgrading entire installations to the indicated SP.
9     **	It is only applicable for downgrade to version 15.7.
10    **
11    ** Parameters
12    **	@dbname	 - The database to be reverted.
13    **	@target	 - The SP to revert to, a string. This version of the procedure
14    **		   accepts 'GA', 'ESDnnn', 'ESD#nnn', or 'SPnnn' for downgrade
15    **		   to any 15.7 SP less than SP100.
16    **	@verbose - Flag, set to 1 to produce verbose output.
17    ** Returns
18    **	0 - success
19    **	1 - error
20    **
21    ** Notes:
22    **  1.	'GA' and 'ESD1' are identical downgrade targets.
23    **  2.	ESD numbers are considered to be (SP / 10), so ESD2 = SP20. We accept
24    **	'dot' ESD releases such as 'ESD#4.2', which would be SP42.
25    **  3.	This version of this procedure is specific to ASE 15.7 SP200.
26    **  4.  Some SP versions are compatible. For example, SP100 through SP110,
27    **	those versions do not have upgrade/downgrade requirements with respect
28    **      to each other.
29    {
30    */
31    
32    create or replace procedure sp_downgrade_esd
33        @dbname sysname = null
34        , @target varchar(10) = 'GA'
35        , @verbose int = 0
36    as
37        begin -- {
38            declare @dbid int
39                , @error_count int
40                , @hi_db_size bigint
41                , @retval int
42                , @maxlen int
43                , @sqlclause varchar(512)
44                , @sqlbuf varchar(1024)
45                , @sqlcmd varchar(1024)
46                , @success int
47                , @this_db_size bigint
48                , @update_val int
49                , @upgd_minor int
50                , @au_spid int
51                , @c int
52                , @our_sbs int
53                , @our_version varchar(10)
54                , @dummy int
55                , @nullarg char(1)
56                , @gp_enabled int
57                , @sp_min int
58                , @sp_now int
59                , @sp_tgt int
60                , @sp_str varchar(10)
61                , @status1 int
62                , @status2 int
63                , @authmech int
64                , @identity_ins int
65                , @identity_upd int
66                , @orig_dbname sysname
67                , @tab_str char(3)
68                , @blank_str char(3)
69                , @pgcount bigint
70                , @create_perm_tables int
71                , @downgrd_sysprot_index int
72                , @dealloc_ftp int
73                , @dbi_logrecstat int
74    
75            -- Initialize constants for this run
76            select @our_version = '15.7'
77                , @sp_min = 200 -- version needing no downgrade support from this version
78                , @sp_now = 200 -- *THIS* version: SP200
79                , @status1 = 1
80                , @status2 = 1
81                , @nullarg = NULL
82                , @authmech = 243
83                , @identity_ins = 83
84                , @identity_upd = 84
85                , @tab_str = '%	%'
86                , @blank_str = '% %'
87                , @downgrd_sysprot_index = 128
88    
89            set nocount on
90    
91            -- If the user wants help, supply it, then stop.
92            select @sp_str = upper(@target)
93    help_me:
94            if @sp_str = 'HELP'
95            begin
96                print 'Usage: sp_downgrade_esd @dbname={db_name} [, @target=[GA | ESD1 | ESD2 | ESD3 | ESD4 | SP10 | SP20 | ... ] [, @verbose=[0|1]]]'
97                print ''
98                print 'Procedure ''sp_downgrade_esd'' makes databases of ASE %1! SP %2! usable by older releases of ASE %3!. It operates on a single database, whose name you provide.',
99                    @our_version, @sp_now, @our_version
100               print 'If ASE is not running in single-user mode, the selected database must be in single-user mode.'
101               return 0
102           end
103   
104           if (@verbose = 0)
105               set nocount on
106   
107           select @success = 1 -- Initially, assume we will fail.
108   
109           /*
110           ** Cannot run downgrade when granular permissions enabled,
111           ** because this procedure could update security tables,
112           ** and update any security catalog is required which is not granted
113           ** to sa_serverprivs_role. 
114           ** SA and SSO roles required. sa_role because it is an administration
115           ** tool and sso_role because of update to security catalogs. 
116           */
117           execute @status1 = sp_aux_checkroleperm 'sa_role',
118               @nullarg, @nullarg, @gp_enabled output
119   
120           if (@gp_enabled = 0)
121           begin
122               execute @status2 = sp_aux_checkroleperm 'sso_role',
123                   @nullarg, @nullarg, @gp_enabled output
124   
125               select @dummy = proc_role('sa_role')
126               select @dummy = proc_role('sso_role')
127   
128               if (@status1 != 0 or @status2 != 0)
129               begin
130                   return (1)
131               end
132           end
133           else
134           begin
135               /* Disable granular permissions before running downgrade */
136               print 'Disable granular permissions before running downgrade'
137               return 1
138           end
139   
140           -- Initial Checks to check sp_downgrade_esd could function.
141   
142           /*
143           ** This version of this procedure is only valid for SP '@sp_now'.
144           ** However, we are willing to accept this procedure being run on any
145           ** ASE that does not need downgrade support from this SP. The oldest
146           ** acceptable version is SP '@sp_min'.
147           */
148           select @our_sbs = convert(int, substring(@@sbssav, 8, 3))
149           if (@our_sbs > @sp_now) or (@our_sbs < @sp_min)
150           begin
151               print 'This version of sp_downgrade_esd is specific to %1! SP %2!. It cannot be used on your current ASE.',
152                   @our_version, @sp_now
153               print 'Your version: %1!', @@version
154               return 1
155           end
156           else if (@verbose = 1)
157           begin
158               print 'Invoking sp_downgrade_esd to downgrade from SP%1! to %2!',
159                   @our_sbs, @target
160           end
161   
162           if db_name() != 'master'
163           begin
164               /*
165               ** 17428, "You must be in the 'master' database in order to change
166               ** database options."
167               */
168               raiserror 17428
169               return 1
170           end
171   
172           /*
173           ** See that we are reverting to a valid version. Keep track of which
174           ** reversions we need to do. We accept 'GA', 'ESDxx', 'ESD#xx', or
175           ** 'SPxxx' as valid target versions. A release's SP number is its
176           ** ESD number * 10, so 'ESD#02' is equivalent to 'SP20'.
177           */
178           select @sp_tgt = - 1
179           if @sp_str = 'GA'
180               select @sp_str = 'SP0'
181           if substring(@sp_str, 1, 3) = 'ESD'
182           begin
183               select @sp_str = substring(@sp_str, 4, 7)
184               if substring(@sp_str, 1, 1) = '#'
185                   select @sp_str = substring(@sp_str, 2, 7)
186               if @sp_str not like '%[^0-9.]%'
187                   select @sp_tgt = convert(int, convert(float, @sp_str) * 10)
188           end
189           else
190           if substring(@sp_str, 1, 2) = 'SP'
191           begin
192               select @sp_str = substring(@sp_str, 3, 20)
193               if @sp_str not like '%[^0-9]%'
194                   select @sp_tgt = convert(int, @sp_str)
195           end
196           if @sp_tgt < 0
197           begin
198               print '"%1!" is not a valid target version.', @target
199               select @sp_str = 'HELP'
200               goto help_me
201           end
202   
203           /*
204           ** Assure that the indicated database exists.
205           */
206           select @dbid = db_id(@dbname)
207           if @dbid is null
208           begin
209               /* 17590, "The specified database does not exist." */
210               raiserror 17590
211               return 1
212           end
213   
214           select @orig_dbname = @dbname
215   
216           /*
217           ** Database name in which there is tab or blank must be delimited
218           ** with '[]' for selection in sql cmd.
219           */
220           if ((patindex(@tab_str, @dbname) > 0)
221                   or (patindex(@blank_str, @dbname) > 0))
222           begin
223               select @dbname = '[' + @orig_dbname + ']'
224           end
225   
226           /*
227           ** We can only revert databases that are writable. Check status
228           ** columns in sysdatabases to validate database suitability.
229           **
230           ** 34272 = 0x85e0
231           ** sysdatabases.status not in 0x20 (in load), 0x40 (not recovered),
232           ** 0x80 (bypass), 0x100 (suspect), 0x400 (rdonly), 0x8000 (emergency)
233           **
234           ** 1040 = 0x0410
235           ** sysdatabases.status2 not in 0x10 (offline), 0x400 (online for
236           ** standby access)
237           **
238           ** 4194459 = 0x40009b
239           ** sysdatabases.status3 not in 0x1 (user proxy), 0x2 (ha proxy),
240           ** 0x8 (shutdown), 0x10 (failedover), 0x80 (quiesced), 0x400000
241           ** (archive)
242           */
243           if exists (select dbid from master.dbo.sysdatabases
244                   where name = @orig_dbname
245                       and (status & 34272 != 0
246                           or status2 & 1040 != 0
247                           or status3 & 4194459 != 0))
248           begin
249               print 'Database ''%1!'' is in a state that does not permit downgrade.',
250                   @dbname
251               return 1
252           end
253   
254           /*
255           ** If the database has been created with asynchronous
256           ** initialization, there should not be any page range
257           ** not initialized.
258           */
259           if exists (select 1 from master.dbo.sysattributes
260                   where class = 42 and object = @dbid)
261           begin
262               print "Database '%1!' is not fully initialized.", @orig_dbname
263               select @au_spid = spid
264               from master..sysprocesses
265               where cmd = 'CRDB AUINIT' and dbid = @dbid
266               if @au_spid is not null
267                   print 'The database is still being initialized asynchronously after a previous CREATE/ALTER DATABASE. You must wait for the Allocation Unit Initializer (spid %1!) to complete.', @au_spid
268               else
269                   print 'The database has not completed asynchronously initialization after a previous CREATE/ALTER DATABASE.  However, the Allocation Unit Initializer is not running. You can start it using DBCC DBREPAIR(%1!, async_database_init, start)', @orig_dbname
270   
271               return 1
272           end
273   
274           /*
275           ** We need either the entire installation or the database itself to be
276           ** in single-user mode.
277           */
278           if (is_singleusermode() = 0)
279               and not exists (select dbid from master.dbo.sysdatabases
280                   where dbid = @dbid
281                       and status & 4096 = 4096)
282           begin
283               print 'Either ASE must be started in single-user mode, or the database to be reverted must be in single-user mode. If you are reverting ''master'' or a temporary database, restart ASE in single-user mode via the ''-m'' command line flag. Otherwise, issue the command:'
284               print 'sp_dboption %1!, ''single user'', true', @orig_dbname
285               return 1
286           end
287   
288           /*
289           ** This is the beginning of the eligibility checking section.
290           ** Here, check for any roadblocks that cannot be fixed by sp_downgrade_esd
291           ** itself
292           */
293   
294           -- Display the current set of features used in the db.
295           --
296           if (@verbose = 1)
297           begin
298               select @nullarg = db_attr(@orig_dbname, 'list_dump_fs')
299               print " "
300           end
301   
302           -- Initialize an error counter for eligibility tests
303           select @error_count = 0
304   
305           /*
306           ** MAINTAINERS please note: reversions should be checked for in
307           ** reverse order, higher ESDs first. Place eligibility checks for
308           ** versions beyond GA+ESD#01 here.
309           */
310   
311           /*
312           ** Handle SP200 features
313           ** Check:
314           ** - If any table/index/index partition is created with index
315           **   compression feature on.
316           */
317           if (@sp_tgt < 200)
318           begin -- {
319   
320               /*
321               ** This block checks for compressed indexes.
322               ** Index compression status info are saved in
323               ** sysobjects.sysstat4
324               ** o 0x0001 = indexes on this table are compressed
325               ** sysindexes.status3
326               ** o 0x200 = this index is defined as compressed
327               ** o 0x400 = this index contains compressed data
328               ** and syspartitions.status
329               ** o 0x200 = index compression is enabled in this
330               **           index partition.
331               */
332               if (@verbose = 1)
333               begin
334                   print 'Checking database ''%1!'' for index compression.', @orig_dbname
335               end
336   
337               select @sqlclause = ' from '
338                   + @dbname + '.dbo.sysobjects o,'
339                   + @dbname + '.dbo.sysindexes i,'
340                   + @dbname + '.dbo.syspartitions p '
341                   + 'where o.id = i.id and o.id = p.id '
342                   + 'and i.indid = p.indid '
343                   + 'and (o.sysstat4 & 1 != 0 or '
344                   + 'i.status3 & 1536 != 0 or '
345                   + 'p.status & 512 != 0)'
346               select @sqlcmd = 'select @retval = count(1) ' + @sqlclause
347               exec (@sqlcmd)
348               if (@retval > 0)
349               begin --{
350                   print "Error: Database '%1!' has tables containing indexes configured for index compression.", @orig_dbname
351                   select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
352                   exec (@sqlcmd)
353                   select @sqlcmd = 'select distinct convert(varchar('
354                       + convert(varchar(10), @retval)
355                       + '), o.name) as ''check these tables'''
356                       + @sqlclause
357                   exec (@sqlcmd)
358                   select @error_count = @error_count + 1
359               end --}
360   
361               /*
362               ** Now new log records are introduced for index
363               ** compression, we cannot continue the downgrade
364               ** if database contains such log records.
365               */
366               select @sqlcmd = 'select @retval=count(*) from '
367                   + @dbname + '.dbo.syslogs '
368                   + 'where op > 86'
369               exec (@sqlcmd)
370               if (@retval > 0)
371               begin --{
372                   print "Error: Database '%1!' contains log records that will not be understood by the target server. Please, truncate the log and retry.", @orig_dbname
373                   select @error_count = @error_count + 1
374               end --}
375           end -- }
376   
377           /*
378           ** Handle SP110 Features (downgrade to anything below SP110)
379           ** Check:
380           ** - If log has records from parallel index-inserts (ISPIU feature)
381           **   those won't be recoverable in prior ASEs.
382           */
383           --	if (@sp_tgt < 110)
384           --	begin -- {
385   
386           /*
387           ** Does db have logrecs from parallel NTAs in the active log
388           ** region? That will prevent loading this db / tran dump into
389           ** SP100 or prior.
390           */
391           --		if (loginfo(@dbname, 'wl_has_pll_ntas_alr') != 0)
392           --		begin
393           --
394           --			print 'CHECKPOINT %1! is needed to clear the transaction log before downgrade.'
395           --				, @orig_dbname
396           --
397           --			if (@verbose = 1)
398           --			begin
399           --				select loginfo(@dbname, 'wl_has_pll_ntas_alr')
400           --					as pll_ntas_in_alr
401           --				     , loginfo(@dbname, 'wl_has_pll_ntas')
402           --				     	as pll_ntas_in_log
403           --			end
404           --		end
405           --	end   -- }
406   
407           /*
408           ** Handle SP100 Features (downgrade to anything below SP100)
409           ** Check:
410           ** - Cumulative dumps must be disabled
411           ** - There are no replication filters in this database
412           ** - There are no database with data holes formed by shrinkdb on the
413           **   database.
414           ** - There are no database objects in between reorg defrag runs.
415           ** - There are no tables having suspect replication indexes.
416           ** - The config option 'max utility parallel degree' is not set to a 
417           **   value > its DEFAULT value (i.e 1), else issue a warning.
418           ** - There are no indexes using RID value comparison.
419           */
420           if (@sp_tgt < 100)
421           begin --{
422               set @sqlbuf = 'sp_downgrade_sysdams'
423               exec @retval = @sqlbuf @dbname, 'prepare', @verbose
424               if (@retval != 0)
425               begin
426                   select @error_count = @error_count + 1
427               end
428   
429               -- Check for replication filters
430               if (@verbose = 1)
431               begin
432                   print 'Checking database ''%1!'' for replication filters.', @orig_dbname
433               end
434   
435               select @sqlcmd = 'select @retval = count(1) from ' + @dbname + '.dbo.sysobjects where
436   			type = ''RF'''
437               exec (@sqlcmd)
438               if (@retval > 0)
439               begin --{
440                   print 'Database %1! contains replication filters. You must drop them before downgrade.', @orig_dbname
441                   select @sqlcmd = 'select @retval = max(datalength(name)) from '
442                       + @dbname + '..sysobjects where type = ''RF'''
443                   exec (@sqlcmd)
444                   select @sqlcmd = 'select distinct convert(varchar('
445                       + convert(varchar(10), @retval) + '), name)
446   				as ''Check these objects'' from ' +
447                       @dbname + '..sysobjects where type = ''RF'''
448                   exec (@sqlcmd)
449                   select @error_count = @error_count + 1
450               end --}	 
451   
452               -- Check that MRP distribution model is not filter
453               if (@verbose = 1)
454               begin
455                   print 'Checking database ''%1!'' for valid ''multipath distribution model'' configuration.', @orig_dbname
456               end
457               select @sqlcmd = 'select @retval = count(1) from ' + @dbname +
458                   '.dbo.sysattributes where class=11 ' +
459                   'and attribute=42 and char_value=''filter'''
460               exec (@sqlcmd)
461               if (@retval > 0)
462               begin --{
463                   print 'Database ''%1!'' Rep Agent configuration ''multipath distribution model'' value ''filter'' is not supported in prior versions.', @orig_dbname
464                   print 'You must change this configuration to a supported value before downgrade.'
465                   select @error_count = @error_count + 1
466               end --}
467   
468               if (@verbose = 1)
469               begin
470                   print 'Checking database ''%1!'' for shrinkdb, defrag downgrade readiness.', @orig_dbname
471               end
472   
473               select @sqlcmd = 'select @pgcount=sum(size) from '
474                   + 'master..sysusages where dbid=@dbid '
475                   + 'and vdevno < 0 and location = 10'
476               exec (@sqlcmd)
477               if (@pgcount > 0)
478               begin --{
479                   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
480                   select @error_count = @error_count + 1
481               end --}
482   
483               set @retval = null
484               select @sqlclause = 'select @maxlen = max(datalength(name)) '
485                   + ' from ' + @dbname
486                   + '..sysobjects so where so.id in (select distinct sa.object'
487               select @sqlbuf = ' from ' + @dbname + '..sysattributes sa where sa.class = 26 and sa.attribute = 2'
488               select @sqlcmd = @sqlclause + @sqlbuf + ')'
489               exec (@sqlcmd)
490               select @sqlclause = 'select convert(varchar('
491                   + convert(varchar(10), @maxlen) + '), name) as '
492                   + '''Run dbcc zapdefraginfo  for these tables affected by reorg defrag.'' '
493                   + ' from ' + @dbname
494                   + '..sysobjects so where so.id in (select distinct sa.object'
495               select @sqlbuf = ' from ' + @dbname + '..sysattributes sa where sa.class = 26 and sa.attribute = 2'
496               select @sqlcmd = 'select @retval = count(distinct sa.object)'
497                   + @sqlbuf
498               exec (@sqlcmd)
499   
500               if @retval != 0
501               begin --{
502                   print 'Error: Database ''%1!'' has tables affected by reorg defrag.', @orig_dbname
503   
504                   --Show names of all the tables affected by reorg defrag in progress.
505   
506                   select @sqlcmd = @sqlclause + @sqlbuf + ')'
507   
508                   print ''
509                   exec (@sqlcmd)
510                   print ''
511   
512                   select @error_count = @error_count + 1
513               end --}
514   
515               /*
516               ** Check for suspect replication indexes made by text
517               ** back-linking.
518               ** 0x8000(32768) on sysindexes.status means index is suspect.
519               ** 0x20(32) on sysindexes.status3 means it's replication index.
520               */
521               if (@verbose = 1)
522               begin
523                   print 'Checking database ''%1!'' for suspect replication indexes.', @orig_dbname
524               end
525               select @sqlbuf = ' from ' + @dbname + '.dbo.sysindexes where status & 32768 != 0'
526               select @sqlcmd = 'select @c = count(indid) '
527                   + 'from ' + @dbname + '.dbo.sysindexes where status & 32768 != 0 '
528                   + 'and status3 & 32 != 0'
529               exec (@sqlcmd)
530               if @c != 0
531               begin --{
532                   print 'Error: Database ''%1!'' contains suspect replication indexes that must be dropped via DBCC REINDEX before downgrade.', @orig_dbname
533                   select @sqlcmd = 'select @c = max(datalength(o.name)) '
534                       + '+ max(datalength(i.name)) + 1 '
535                       + 'from ' + @dbname + '.dbo.sysobjects o,'
536                       + @dbname + '.dbo.sysindexes i '
537                       + 'where i.id = o.id and i.status & 32768 != 0'
538                       + 'and i.status3 & 32 != 0'
539                   exec (@sqlcmd)
540                   select @sqlcmd = 'select convert(varchar(' + convert(varchar(10), @c)
541                       + '), o.name + ''.'' + i.name) as ''Drop these indexes:'''
542                       + 'from ' + @dbname + '.dbo.sysobjects o,'
543                       + @dbname + '.dbo.sysindexes i '
544                       + 'where i.id = o.id and i.status & 32768 != 0'
545                       + 'and i.status3 & 32 != 0'
546                   print ''
547                   exec (@sqlcmd)
548                   print ''
549   
550                   select @error_count = @error_count + 1
551               end --}
552   
553               /*
554               ** Warn user if max utility parallel degree value in database master > its DEFAULT
555               ** value (i.e 1), since this is a newly introduced parameter from
556               ** asecarina (15.7 SP100) onwards. This applies if we are downgrading 'master'.
557               */
558               if @dbid = 1
559               begin --{
560                   select @sqlcmd = 'select @c = value from master.dbo.sysconfigures where config=587'
561                   exec (@sqlcmd)
562                   if (@c > 1)
563                   begin --{
564                       if (@verbose = 1)
565                       begin
566                           print 'Checking for config parameter downgrade readiness.', @orig_dbname
567                       end
568                       print 'Warning: Config parameter ''max utility parallel degree'' is set to ''%1!'' ', @c
569                       print ''
570                       print 'This parameter will be removed after downgrade.'
571                       print ''
572                       print 'Please set ''max parallel degree'' accordingly for CREATE INDEX and UPDATE STATISTICS to run in parallel'
573                       print ''
574                   end --}
575               end --}
576   
577               -- This database can't have indexes using RID value comparison.
578               -- Bit IND_RID_VALUECMP(256) in sysindexes.status
579               -- indicates the index using RID value comparision.
580               select @sqlcmd = 'select @retval=count(*) from '
581                   + @dbname + '..sysindexes '
582                   + 'where ((status & 256) = 256) '
583               exec (@sqlcmd)
584               if (@retval > 0)
585               begin --{
586                   print "Error: Database '%1!' (dbid: %2!) has indexes using RID value comparison. Please drop these indexes and retry.", @orig_dbname, @dbid
587                   print " "
588   
589                   -- Display the names of all the indexes with such
590                   -- setting in this databases.
591                   set @sqlcmd = @dbname + ".dbo.sp_dwngd_helpindex"
592                   exec @sqlcmd
593   
594                   print ""
595   
596                   select @error_count = @error_count + 1
597               end --}
598           end --}
599   
600           /*
601           ** Handle ESD#2 Features (to downgrade to ESD#1 or GA)
602           */
603           if (@sp_tgt < 20)
604           begin --{
605               -- This database cannot be larger than 2^31 logical pages
606               select @hi_db_size = 2
607               select @hi_db_size = power(@hi_db_size, 31)
608                   , @this_db_size = sum(size)
609               from master.dbo.sysusages
610               where dbid = @dbid
611               if (@this_db_size > @hi_db_size)
612               begin
613                   print 'For compatibility with GA or ESD#1, databases cannot be larger than %1! pages.',
614                       @hi_db_size
615                   select @error_count = @error_count + 1
616               end
617   
618               -- This database cannot have nonmaterialized tables.
619               -- sysobjects.sysstat3 bit 128 is set for those tables.
620               select @sqlcmd = 'select @retval = max(datalength(name)) '
621                   + 'from ' + @dbname + '.dbo.sysobjects '
622                   + 'where type = ''U'' and sysstat3 & 128 != 0'
623               exec (@sqlcmd)
624               if @retval is not null
625               begin
626                   print 'Error: Database %1! has one or more deferred tables.',
627                       @orig_dbname
628                   select @sqlcmd = 'select convert(varchar(' + convert(varchar(10), @retval)
629                       + '), name) as ''Materialize or drop the following deferred tables:'' '
630                       + 'from ' + @dbname + '.dbo.sysobjects o '
631                       + 'where type = ''U'' and sysstat3 & 128 != 0'
632                   exec (@sqlcmd)
633                   print ''
634                   select @error_count = @error_count + 1
635               end
636   
637               -- This database cannot have precomputed result set.
638               -- sysobjects.type is 'RS' for those precomputed result set.
639               -- Determine if any PRS is present in the database.
640               select @sqlcmd = 'select @retval = max(datalength(name)) '
641                   + 'from ' + @dbname + '.dbo.sysobjects '
642                   + 'where type = ''RS'' '
643               exec (@sqlcmd)
644               if @retval is not null
645               begin --{
646                   print 'Database ''%1!'' contains one or more precomputed result sets.', @orig_dbname
647   
648                   select @sqlcmd = 'select convert(varchar(' + convert(varchar(10), @retval)
649                       + '), name) as ''Drop these precomputed result sets:'' '
650                       + 'from ' + @dbname + '.dbo.sysobjects '
651                       + 'where type = ''RS'' '
652   
653                   --Show names of all the precomputed result sets
654                   print ''
655                   exec (@sqlcmd)
656   
657                   print ''
658                   print 'Error: All the precomputed result sets listed above should be dropped before downgrade.'
659                   print ''
660   
661                   select @error_count = @error_count + 1
662               end --}
663   
664               -- This database cannot have tables affected by no-datacopy-drop-column operation
665               select @sqlclause = ' from ' + @dbname + '..sysattributes sattr where sattr.object_type=''TI'''
666                   + ' and sattr.object_info1 & 4096 = 4096'
667               select @sqlcmd = 'select @retval = max(datalength(sattr.object_cinfo2))' + @sqlclause
668               exec (@sqlcmd)
669   
670               if @retval is not null
671               begin --{
672                   print 'Error: Database ''%1!'' has tables affected by no-datacopy-column drop.', @orig_dbname
673   
674                   --Show names of all the tables affected by no-datacopy-drop-column operation
675                   select @sqlcmd = 'select distinct convert(varchar('
676                       + convert(varchar(10), @retval)
677                       + '),sattr.object_cinfo2) as ''Run Reorg Rebuild for these tables affected by no-datacopy-drop-column operation:'''
678                       + @sqlclause
679   
680                   print ''
681                   exec (@sqlcmd)
682                   print ''
683   
684                   select @error_count = @error_count + 1
685               end --}
686   
687               --
688               -- This database cannot have IRL compressed tables.
689               --
690               select @sqlcmd = 'select @retval = count(1) from ' + @dbname
691                   + '..syscolumns where id = object_id(''sysobjects'') and name = ''lobcomp_lvl'''
692               exec (@sqlcmd)
693               if (@retval > 0)
694               begin --{
695                   /*    
696                   ** The 1st block is a check for compressed tables.
697                   ** sysstat3 value 28672 is 0x7000, the three compression
698                   ** status bits:
699                   **  - 0x1000 = Table is row level compressed
700                   **  - 0x2000 = Table is page level compressed
701                   **  - 0x4000 = Table contains compressed data
702                   **
703                   ** The 2nd block is a check for LOB compressed columns.
704                   ** syscolumns.status2 value 131072 is 0x20000,
705                   ** "column should not be compressed".
706                   ** sysobjects.sysstat3 value 2048 is 0x800,
707                   ** "table contains compressed LOB data".
708                   **
709                   ** The 3rd block is a check for 15.7 tables containing
710                   ** in-row LOBs.
711                   */
712                   select @sqlclause = ' from '
713                       + @dbname + '..syscolumns c,'
714                       + @dbname + '..sysobjects o,'
715                       + @dbname + '..syspartitions p'
716                       + ' where o.id = c.id and o.id = p.id'
717                       + ' and (o.sysstat3 & 28672 != 0'
718                       + '      or (p.ptndcompver is not null and p.ptndcompver > 0))'
719                       + ' and ((c.lobcomp_lvl is not null and c.status2 & 131072 = 0)'
720                       + '      or ((o.lobcomp_lvl is not null and o.lobcomp_lvl > 0) or (o.sysstat3 & 2048 != 0))'
721                       + '      or (p.lobcomp_lvl is not null and p.lobcomp_lvl > 0))'
722                       + ' and (c.inrowlen is not null) and (c.inrowlen > 0)'
723   
724                   select @sqlcmd = 'select @retval = count(1) ' + @sqlclause
725   
726                   exec (@sqlcmd)
727                   if (@retval > 0)
728                   begin --{
729                       print 'Error: Database ''%1!'' has tables configured for compressed in-row LOBs.', @orig_dbname
730                       select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
731                       exec (@sqlcmd)
732                       select @sqlcmd = 'select distinct convert(varchar('
733                           + convert(varchar(10), @retval)
734                           + '), o.name) as ''check these tables'''
735                           + @sqlclause
736                       exec (@sqlcmd)
737                       print 'For each table, set compression = none, then use ''reorg rebuild'' on that table.'
738                       select @error_count = @error_count + 1
739                   end --}
740   
741               end --}
742   
743               -- This database cannot be configured to have option
744               -- 'deallocate first text page' turned ON, which means deallocate
745               -- first text page after NULL update
746               --
747               select @dealloc_ftp = number
748               from master.dbo.spt_values
749               where type = 'D4' and name = 'deallocate first text page'
750   
751               if exists (select name
752                       from master.dbo.sysdatabases
753                       where status4 & @dealloc_ftp != 0)
754               begin
755                   print 'Error: Database ''%1!'' has option ''deallocate first text page'' turned on, run sp_dboption to turn off this option and retry.', @dbname
756                   select name as 'check these databases'
757                   from master.dbo.sysdatabases
758                   where status4 & @dealloc_ftp != 0
759                   select @error_count = @error_count + 1
760               end
761   
762               -- This database cannnot have tables which has option
763               -- "dealloc_firxt_txtpg" of config value 2, which means don't
764               -- deallocate first text page after NULL update
765               --
766               select @sqlclause = ' from ' + @dbname + '.dbo.sysobjects o '
767                   + 'where o.sysstat2 & -2147483648 = -2147483648'
768               select @sqlcmd = 'select @retval = max(datalength(o.name))' + @sqlclause
769               exec (@sqlcmd)
770               if @retval is not null
771               begin --{
772                   print "Error: Database '%1!' (dbid: %2!) has tables configured not to deallocate first text page after NULL update.", @orig_dbname, @dbid
773   
774                   /* Display the names of all the tables with such setting in this database */
775                   select @sqlcmd = 'select convert(varchar(' + convert(varchar(10), @retval)
776                       + '),o.name) as ''Run sp_chgattribute to change dealloc_firxt_txt_pg to 0 or 1 for following tables:'' '
777                       + @sqlclause
778                   exec (@sqlcmd)
779                   print ""
780   
781                   select @error_count = @error_count + 1
782               end --} 
783           end --}
784   
785           -- *NOTE* that ESD#1 and GA are identical for downgrade purposes.
786   
787           if @error_count != 0
788           begin
789               -- Something failed, so we fail.
790               print 'One or more checks failed. Reversion cannot be completed.'
791               print 'Re-run sp_downgrade_esd after addressing the items listed above.'
792   
793               -- Show the active features, for debugging.
794               select @nullarg = db_attr(@orig_dbname, 'list_dump_fs')
795               print " "
796               return @success
797           end
798   
799           /*
800           ** We need to update system tables. Make sure that is allowed.
801           */
802           select @update_val = value
803           from master.dbo.sysconfigures
804           where name = 'allow updates to system tables'
805           if @update_val = 0
806           begin
807               exec sp_configure 'allow updates to system tables', 1
808           end
809   
810           /*
811           ** Initial checks look good. Try the downgrade.
812           */
813           print 'Reverting database ''%1!'' to %2!.', @orig_dbname, @target
814   
815           -- Checkpoint now, so as to rid the log of portions that cannot
816           -- be recovered in an older version.
817           --
818           exec sp_getmessage 19112, @sqlbuf output
819           print @sqlbuf, @dbname, 'downgrade'
820   
821           checkpoint @dbname
822           if (@@error != 0)
823           begin
824               exec sp_getmessage 19113, @sqlbuf output
825               print @sqlbuf, @dbname
826               select @error_count = @error_count + 1
827           end
828   
829           /*
830           ** This table contains reversion steps. The columns are:
831           ** - dbid, the database ID that this reversion step applies to; can be
832           **	the current db (@dbid) or a hard-coded database ID
833           ** - tgt, the target for reversion, the SP number that added this item:
834           **	for instance, 40 for ESD4/SP40, 100 for SP100. Downgrade will
835           **	happen for items added in versions higher than the user's
836           **	specified downgrade target.
837           ** - cmd, the command to execute if this step applies to this reversion
838           */
839           create table #downgrade_esd(
840               dbid int
841               , tgt int
842               , cmd varchar(1024)
843           ) lock allpages
844   
845           if (@sp_tgt < 200)
846           begin -- {
847               -- Remove new added column sysobjects.sysstat4 if going to < 15.7
848               insert #downgrade_esd values (@dbid, 200,
849                   'update ' + @dbname + '.dbo.sysobjects set sysstat4 = null where sysstat4 is not null ' +
850                   'delete from ' + @dbname + '.dbo.syscolumns where id = object_id(''sysobjects'') ' +
851                   'and name in (''sysstat4'')')
852           end -- }
853   
854           if (@sp_tgt < 110)
855           begin -- {
856               -- Here, insert into #downgrade_esd commands that may be
857               -- needed to undo changes made by SP110.
858   
859               -- In each db, turn off footprint of feature that shows
860               -- that parallel NTAs were run in this db.
861               -- Turn OFF: DBI_LOG_HAS_PLL_NTA_XACTS.
862               --
863               insert into #downgrade_esd values (@dbid, 110,
864                   'declare @ignore int '
865                   + 'select @ignore = dbinfo_update('''
866                   + @dbname
867                   + ''', ''logrecstat'', '
868                   + '(dbinfo_get(''' + @dbname + ''', ''logrecstat'')'
869                   + ' & ~4)'
870                   + ')'
871               )
872   
873           end -- }
874   
875           if (@sp_tgt < 100)
876           begin --{
877               -- Here, insert into #downgrade_esd commands that may be
878               -- needed to undo changes made by SP100.
879   
880               insert into #downgrade_esd values (@dbid, 100,
881                   'exec @retval = sp_downgrade_sysdams @dbname, ' +
882                   'downgrade, @verbose')
883   
884               -- In each db, turn off "text back links are maintained" indicator.
885               insert into #downgrade_esd values (@dbid, 100,
886                   'update ' + @dbname + '.dbo.syspartitions '
887                   + 'set status = status - 256 '
888                   + 'where status & 256 != 0')
889   
890               /*
891               ** Drop RAT properties:
892               **   - 'multiple scanners'
893               **   - 'max schema cache per scanner'
894               **   - 'trunc point request interval'
895               */
896               insert into #downgrade_esd values (@dbid, 100,
897                   'delete ' + @dbname + '.dbo.sysattributes '
898                   + 'where class=11 and attribute in (43, 44, 45)')
899   
900               /*
901               ** Fix RAT MRP filter mode specific properties:
902               **   - drop all filter bindings (this step shouldn't
903               **     ever encounter rows since the filters need to
904               **     be dropped in the 'prepare' step, but we will
905               **     make sure we have no orphans left around).
906               */
907               insert into #downgrade_esd values (@dbid, 100,
908                   'delete ' + @dbname + '.dbo.sysattributes '
909                   + 'where class=41 and attribute=24')
910   
911               /*
912               ** Drop HADR related items 
913               ** 	- from sysattributes for HADR class
914               **	- from sysservers for HADR MEMBER & HADR GROUP class
915               */
916               insert into #downgrade_esd values (@dbid, 100,
917                   'delete ' + @dbname + '.dbo.sysattributes '
918                   + 'where class=43 and attribute in (0,1,2)')
919   
920               insert into #downgrade_esd values (1, 100,
921                   'delete master..sysservers '
922                   + 'where srvclass in (16,17)')
923   
924               /* 
925               ** Delete config options 
926               ** 'HADR mode'
927               ** 'execution time monitoring'
928               ** 'threshold event monitoring'
929               ** 'threshold event max messages'
930               */
931               insert into #downgrade_esd values (1, 100,
932                   'delete master..sysconfigures where config in (581, 583, 600, 601)')
933   
934               /* 
935               ** Delete config option
936               ** 'max utility parallel degree'
937               */
938               insert into #downgrade_esd values (1, 100,
939                   'delete master..sysconfigures where config=587')
940   
941               /* 
942               ** Delete config option
943               ** 'utility statistics hashing'
944               */
945               insert into #downgrade_esd values (1, 100,
946                   'delete master..sysconfigures where config=580')
947   
948               /*
949               ** Delete config option
950               ** 'optimize temp table resolution'
951               */
952               insert into #downgrade_esd values (1, 100,
953                   'delete master..sysconfigures where config=591')
954   
955               /* Drop the monSysExecutionTime table from master database */
956               insert into #downgrade_esd values (1, 100,
957                   'if exists (select 1 from master..sysobjects '
958                   + 'where name = ''monSysExecutionTime'') '
959                   + 'begin '
960                   + 'drop table monSysExecutionTime '
961                   + 'end ')
962   
963               /* Drop the monThresholdEvent table from master database */
964               insert into #downgrade_esd values (1, 100,
965                   'if exists (select 1 from master..sysobjects '
966                   + 'where name = ''monThresholdEvent'') '
967                   + 'begin '
968                   + 'drop table monThresholdEvent '
969                   + 'end ')
970   
971               /* 
972               ** Clear DBT_TXT_LINKED bit on sysdatabases.status.
973               ** For master db, we also need to clear this bit on all
974               ** the temporary databases since if applying downgrade_esd
975               ** to master db, we think customer plan to downgrade
976               ** to lower esd version, so there is the chance to clear
977               ** this bit on temp dbs.
978               ** Besides tempdb, there are some other types of temp dbs:
979               ** 0x100(for SDC, it means local user temp db, for SMP,
980               ** it means user created temp db, 0x20000000(local system
981               ** temp db), 0x40000000(global temp db).
982               */
983               -- If we are downgrading master, clear status for temp dbs
984               insert into #downgrade_esd values (1, 100,
985                   'update master.dbo.sysdatabases '
986                   + 'set status = status - 1 '
987                   + 'where (name = ''tempdb'' '
988                   + 'or status3 & 256 = 256 '
989                   + 'or status3 & 536870912 = 536870912 '
990                   + 'or status3 & 1073741824 = 1073741824) '
991                   + 'and status & 1 != 0 ')
992               -- For any database, clear status for the indicated db
993               insert into #downgrade_esd values (@dbid, 100,
994                   'update master.dbo.sysdatabases '
995                   + 'set status = status - 1 '
996                   + 'where status & 1 != 0 '
997                   + 'and dbid = ' + convert(varchar(30), @dbid))
998   
999               /* Remove new upgrade items if going to < 15.7 SP100 */
1000              insert into #downgrade_esd values (@dbid, 100,
1001                  'delete ' + @dbname + '.dbo.sysattributes '
1002                  + 'where class=1 and attribute in (82, 1731)')
1003          end --} ESD4 
1004  
1005          if (@sp_tgt < 40)
1006          begin --{
1007              -- Here, insert into #downgrade_esd commands that may be
1008              -- needed to go to ESD#3 or below. For example:
1009              -- insert into #downgrade_esd values (@dbid, 40, 'select 1')
1010              select @error_count = @error_count
1011          end --} ESD3
1012  
1013          if (@sp_tgt < 30)
1014          begin --{
1015              -- Here, insert into #downgrade_esd commands that may be
1016              -- needed to go to ESD#2 or below. For example:
1017              -- insert into #downgrade_esd values (@dbid, 20, 'select 1')
1018              select @error_count = @error_count
1019          end --} ESD2
1020  
1021          if (@sp_tgt < 20)
1022          begin --{
1023              -- Reverting to ESD#1 or GA.
1024              -- Remember that we need to drop some additional temp tables.
1025              select @create_perm_tables = 1
1026  
1027              /* 
1028              ** Create temporary table to store grantee info 
1029              ** who are system roles, sa_serverprivs_role or dbo.
1030              */
1031              create table #grantee(uid int, name varchar(255))
1032  
1033              /* Create temporary table to store user info for printing */
1034              create table #users(uid int, name varchar(255))
1035  
1036              /* Create temporary table to store table info for printing */
1037              create table #tables(id int, objname varchar(255))
1038  
1039              /* 
1040              ** Create temporary table to store permission info for
1041              ** grantees who are not system roles, UDR sa_serverprivs_role or dbo.
1042              */
1043              create table #perm(id int, grantee int, grantee_name varchar(255),
1044                  action smallint, type tinyint, grantor int)
1045  
1046              /*
1047              ** If there are GP permissions granted to grantees other than
1048              ** system roles, sa_serverprivs_role and dbo, we need to let the user 
1049              ** know this and exit so that they can take care of getting rid of this
1050              ** data. We do not want to get rid of it for them. We also want to 
1051              ** report the permission for builtin authmech as this will be deleted 
1052              ** since it is a new permission added for 15.7 ESD2.
1053              */
1054              -- Populate temp table with users needed for display 
1055              select @sqlcmd = 'insert into #users(uid,name)'
1056                  + ' select u.uid, u.name from '
1057                  + @dbname + '.dbo.sysusers u'
1058              exec (@sqlcmd)
1059  
1060              -- Populate temp table with tables needed for display 
1061              select @sqlcmd = 'insert into #tables(id,objname)'
1062                  + ' select t.id, t.name from '
1063                  + @dbname + '.dbo.sysobjects t'
1064              exec (@sqlcmd)
1065  
1066              -- Check for GP permissions granted to grantees who are
1067              -- not system roles, sa_serverprivs_role or dbo
1068              select @sqlcmd = 'insert into #grantee(uid,name)'
1069                  + ' select distinct u.uid, u.name from '
1070                  + @dbname + '.dbo.sysusers u,' + @dbname + '.dbo.sysroles r'
1071                  + ' where u.uid = 1'
1072                  + ' or (u.uid = r.lrid'
1073                  + ' and (r.id <= 31 or r.id = role_id(''sa_serverprivs_role'')))'
1074              exec (@sqlcmd)
1075  
1076              select @sqlcmd = 'insert into #perm(id,grantee,grantee_name,'
1077                  + ' action,type,grantor)'
1078                  + ' select distinct p.id, p.uid, u.name, p.action,'
1079                  + ' p.protecttype, p.grantor'
1080                  + ' from ' + @dbname + '.dbo.sysprotects p,'
1081                  + ' master.dbo.spt_values c,' + @dbname + '.dbo.sysusers u'
1082                  + ' where p.action = c.number'
1083                  + ' and (c.type = ''GP'' or p.id = @authmech)'
1084                  + ' and c.number <= 1024'
1085                  + ' and p.uid not in (select uid from #grantee)'
1086                  + ' and p.uid = u.uid'
1087              exec (@sqlcmd)
1088  
1089              if (@dbid = 1)
1090              begin --{
1091                  -- Check for master specific dbcc perms
1092                  -- as these will be also deleted later on
1093                  -- as they are not supported in < 15.7 ESD2.
1094                  select @sqlcmd = 'insert into #perm(id,grantee,'
1095                      + ' grantee_name,action,type,grantor)'
1096                      + ' select distinct p.id, p.uid, u.name, p.action,'
1097                      + ' p.protecttype, p.grantor'
1098                      + ' from ' + @dbname + '.dbo.sysprotects p,'
1099                      + ' master.dbo.spt_values c,'
1100                      + @dbname + '.dbo.sysusers u'
1101                      + ' where p.action = c.number'
1102                      + ' and c.type = ''T'''
1103                      + ' and c.number <= 1024'
1104                      + ' and c.msgnum & 16386 = 16386'
1105                      + ' and p.uid not in (select uid from #grantee)'
1106                      + ' and p.uid = u.uid'
1107                  exec (@sqlcmd)
1108              end --}
1109  
1110              if ((select count(*) from #perm) > 0)
1111              begin --{
1112                  -- List permissions and exit
1113                  select distinct
1114                      grantor = u.name,
1115                      grantee = p.grantee_name,
1116                      type = case
1117                          when p.type = 0
1118                          then 'Grant with grant option'
1119                          when p.type = 1
1120                          then 'Grant'
1121                          when p.type = 2
1122                          then 'Revoke'
1123                      end,
1124                      permission = a.name,
1125                      object = case
1126                          when p.id = 0
1127                          then ''
1128                          when (p.id = @authmech)
1129                          then (select c.name
1130                              from master.dbo.spt_values c
1131                              where c.number = p.id
1132                                  and c.type = 'BI')
1133                          when (p.action = @identity_ins
1134                                  or p.action = @identity_upd)
1135                          then (select t.objname
1136                              from #tables t
1137                              where t.id = p.id)
1138                          else db_name(p.id)
1139                      end
1140                  into #permissions
1141                  from #perm p, master.dbo.spt_values a, #users u
1142                  where (a.type = 'T' or a.type = 'GP')
1143                      and a.number = p.action
1144                      and u.uid = p.grantor
1145  
1146                  print ''
1147                  print 'Database ''%1!'' contains permissions new to 15.7 ESD#2 granted to users, roles or groups.', @orig_dbname
1148                  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.'
1149                  print ''
1150  
1151                  exec sp_autoformat @fulltabname = #permissions
1152  
1153                  drop table #permissions
1154  
1155                  goto cleanup
1156              end --}
1157  
1158              /* 
1159              ** Get rid of the explicit builtin and dbcc permissions which
1160              ** have been granted to sa_role and dbo. Else while upgrading
1161              ** to 15.7 ESD2, we will get duplicate key insertion error as
1162              ** upgrade adds these explicit permissions.
1163              */
1164              if (@dbid = 1)
1165              begin --{
1166                  /* Delete the BI permissions granted to sa_role */
1167                  select @sqlcmd = 'delete master.dbo.sysprotects'
1168                      + ' from master.dbo.sysprotects p,'
1169                      + ' master.dbo.sysroles r'
1170                      + ' where p.uid = r.lrid'
1171                      + ' and r.id = role_id(''sa_role'')'
1172                      + ' and p.id in (81,82,83,84)'
1173                  exec (@sqlcmd)
1174  
1175                  /* Delete the any DBCC permissions granted to sa_role */
1176                  select @sqlcmd = 'delete master.dbo.sysprotects'
1177                      + ' from master.dbo.sysprotects p,'
1178                      + ' master.dbo.spt_values v,'
1179                      + ' master.dbo.sysroles r'
1180                      + ' where p.action = v.number'
1181                      + ' and r.lrid = p.uid'
1182                      + ' and r.id = role_id(''sa_role'')'
1183                      + ' and v.type = ''T'''
1184                      + ' and (v.msgnum & 16385 = 16385)'
1185                  exec (@sqlcmd)
1186              end --}
1187              else
1188              begin --{
1189                  /* 
1190                  ** Delete the database specific DBCC permissions granted
1191                  ** to dbo 
1192                  */
1193                  select @sqlcmd = 'delete ' + @dbname + '.dbo.sysprotects'
1194                      + ' from ' + @dbname + '.dbo.sysprotects p,'
1195                      + ' master.dbo.spt_values v'
1196                      + ' where p.action = v.number'
1197                      + ' and p.uid = 1'
1198                      + ' and v.type = ''T'''
1199                      + ' and (v.msgnum & 16386 = 16386)'
1200                  exec (@sqlcmd)
1201              end --}
1202  
1203              /* 
1204              ** Create a temporary table which holds all the info on 
1205              ** permissions for builtin functions
1206              */
1207              create table #perm_bi_t_tab(uid int not null,
1208                  grantor int not null,
1209                  ptype tinyint not null)
1210  
1211              select @sqlcmd = 'insert into #perm_bi_t_tab'
1212                  + ' select distinct uid, grantor, protecttype'
1213                  + ' from ' + @dbname + '.dbo.sysprotects'
1214                  + ' where id in (81,82,83,84)'
1215                  + ' order by uid, grantor, protecttype'
1216              exec (@sqlcmd)
1217  
1218              /* 
1219              ** Create a temporary table which holds all the info on 
1220              ** permissions for dbcc commands 
1221              */
1222              create table #perm_dbcc_t_tab(uid int not null,
1223                  grantor int not null)
1224  
1225              select @sqlcmd = 'insert into #perm_dbcc_t_tab'
1226                  + ' select distinct uid, grantor'
1227                  + ' from ' + @dbname + '.dbo.sysprotects, master.dbo.spt_values'
1228                  + ' where action = number'
1229                  + ' and type = ''T'''
1230                  + ' and (msgnum & 16386 = 16386 or msgnum & 16385 = 16385)'
1231                  + ' order by uid, grantor'
1232              exec (@sqlcmd)
1233  
1234              /* 
1235              ** Convert builtin permissions to < 15.7 ESD2 format 
1236              ** 	- There will be only row for all builtin permissions
1237              ** 	  granted to a particular grantee. So many rows in ESD2
1238              ** 	  will have to be converted to one row
1239              **	- sysprotects.id in ESD2 has the actual builtin id for
1240              **	  FGAC builtins. We need to insert BUILTIN_ID which is 
1241              **	  -255 for id column
1242              **	- In ESD2, column map in sysprotects is null. But the 
1243              **	  builtins will have to be converted to bitmap in the 
1244              **	  column map
1245              **	- In ESD2, sysprotects.action has PERM_SELECT_BI which 
1246              **	  is 5 whereas in < ESD2, it is SELECT.
1247              **
1248              ** Convert dbcc permissions to < 15.7 ESD2 format 
1249              ** 	- There will be only row for all dbcc permissions
1250              ** 	  granted to a particular grantee. So many rows in ESD2
1251              ** 	  will have to be converted to one row
1252              **	- sysprotects.id in ESD2 has 0. It has to be changed to
1253              **	  -317 
1254              **	- In ESD2, column map in sysprotects is null. But the 
1255              **	  dbcc cmd will have to be converted to bitmap in the 
1256              **	  column map
1257              **	- In ESD2, sysprotects.action has the actual permission 
1258              **	  associated with the DBCC commamnd whereas in < ESD2, 
1259              **	  it is 317.
1260              */
1261              if (exists (select * from #perm_bi_t_tab)
1262                      or exists (select * from #perm_dbcc_t_tab))
1263              begin
1264                  dbcc downgd_bi_dbcc_perms(@orig_dbname, '#perm_bi_t_tab',
1265                      '#perm_dbcc_t_tab')
1266              end
1267  
1268              insert into #downgrade_esd values (1, 20,
1269                  'update master.dbo.syscolumns set type=38, usertype=7 '
1270                  + 'where id = 31 and name = ''unreservedpgs'' '
1271                  + 'select @retval = @@error '
1272                  + 'update master.dbo.syscolumns set type=56, usertype=7 '
1273                  + 'where id = 31 and name in (''lstart'', ''size'') '
1274                  + 'select @retval = @retval + @@error '
1275                  + 'select @c=set_index_key_type(1, 31, 3, 2, 56)'
1276                  + 'select @retval = @retval + @@error ')
1277              insert into #downgrade_esd values (1, 20,
1278                  'update master.dbo.syscolumns '
1279                  + 'set type=56, usertype=7 where id=34 and usertype=45 and name=''page''')
1280              insert into #downgrade_esd values (1, 20,
1281                  'update master.dbo.syscolumns '
1282                  + 'set type=56, usertype=7 where id=50 and usertype=45 and name=''page''')
1283              insert into #downgrade_esd values (1, 20,
1284                  'update master.dbo.syscolumns '
1285                  + 'set type=56, usertype=7 where id=89 and usertype=45 and name in (''lstart'', ''size'')')
1286              insert into #downgrade_esd values (@dbid, 20,
1287                  'update ' + @dbname + '.dbo.syscolumns set type=56, usertype=7 '
1288                  + 'where id=18 and usertype=45 and name=''free_space''')
1289              insert into #downgrade_esd values (@dbid, 20,
1290                  'update ' + @dbname + '.dbo.syscolumns set type=56, usertype=7 '
1291                  + 'where id=28 and usertype=45 and '
1292                  + 'name in (''datoampage'',''indoampage'',''firstpage'',''rootpage'')')
1293              insert into #downgrade_esd values (@dbid, 20,
1294                  'update ' + @dbname + '.dbo.syscolumns '
1295                  + 'set type=56, usertype=7 where id=23 and usertype=45 and '
1296                  + 'name in (''leafcnt'',''pagecnt'',''emptypgcnt'','
1297                  + '''warmcachepgcnt'',''unusedpgcnt'',''oampagecnt'')')
1298  
1299              /* More Predicated Privileges and Granular Permissions downgrade */
1300  
1301              /* Delete all predicated privileges from sysprotects */
1302              insert into #downgrade_esd values (@dbid, 20,
1303                  'delete ' + @dbname + '.dbo.sysprotects '
1304                  + 'where predid is not NULL')
1305  
1306              /* Delete all role activation predicates from sysloginroles */
1307              insert into #downgrade_esd values (1, 20,
1308                  'delete master.dbo.sysloginroles '
1309                  + 'where predid is not NULL')
1310  
1311              /* Delete all predicates from syscomments */
1312              insert into #downgrade_esd values (@dbid, 20,
1313                  'delete ' + @dbname + '.dbo.syscomments '
1314                  + 'from ' + @dbname + '.dbo.syscomments c,'
1315                  + @dbname + '.dbo.sysobjects o '
1316                  + 'where o.type=''RP'' and '
1317                  + 'o.id=c.id')
1318  
1319              /* Delete all predicates from sysprocedures */
1320              insert into #downgrade_esd values (@dbid, 20,
1321                  'delete ' + @dbname + '.dbo.sysprocedures '
1322                  + 'from ' + @dbname + '.dbo.sysprocedures p,'
1323                  + @dbname + '.dbo.sysobjects o '
1324                  + 'where o.type=''RP'' and '
1325                  + 'o.id=p.id')
1326  
1327              /* Delete all predicates from sysdepends */
1328              insert into #downgrade_esd values (@dbid, 20,
1329                  'delete ' + @dbname + '.dbo.sysdepends '
1330                  + 'from ' + @dbname + '.dbo.sysdepends d,'
1331                  + @dbname + '.dbo.sysobjects o '
1332                  + 'where o.type=''RP'' and '
1333                  + 'o.id=d.id')
1334  
1335              /* Delete all predicates from sysobjects */
1336              insert into #downgrade_esd values (@dbid, 20,
1337                  'delete ' + @dbname + '.dbo.sysobjects '
1338                  + 'where type=''RP''')
1339  
1340              /* 
1341              ** Delete new columns predid and status from sysprotects added
1342              ** for predicated privileges
1343              */
1344              insert into #downgrade_esd values (@dbid, 20,
1345                  'delete ' + @dbname + '.dbo.syscolumns '
1346                  + 'where id=9 and '
1347                  + 'name in (''predid'',''status'')')
1348  
1349              /* 
1350              ** Delete new columns predid from sysloginroles added
1351              ** for activation predicates 
1352              */
1353              insert into #downgrade_esd values (1, 20,
1354                  'delete master..syscolumns '
1355                  + 'where id=49 and '
1356                  + 'name=''predid''')
1357  
1358              /* Delete all permissions granted by usedb_user */
1359              insert into #downgrade_esd values (@dbid, 20,
1360                  'delete ' + @dbname + '.dbo.sysprotects '
1361                  + 'from ' + @dbname + '.dbo.sysprotects p, #users u'
1362                  + ' where p.grantor=u.uid and u.name = ''usedb_user''')
1363  
1364              /* Drop user usedb_user */
1365              insert into #downgrade_esd values (@dbid, 20,
1366                  'delete ' + @dbname + '.dbo.sysusers '
1367                  + 'where name=''usedb_user''')
1368  
1369              /* Delete new builtin permission for authmech */
1370              insert into #downgrade_esd values (@dbid, 20,
1371                  'delete ' + @dbname + '.dbo.sysprotects '
1372                  + 'where action=5 and id=@authmech')
1373  
1374              insert into #downgrade_esd values (1, 20,
1375                  'delete ' + @dbname + '.dbo.sysattributes '
1376                  + 'where class=1 and attribute in (1648,1649)')
1377  
1378              /* Remove GP sysattributes values if going to < 15.7 ESD#2 */
1379              insert into #downgrade_esd values (@dbid, 20,
1380                  'delete ' + @dbname + '.dbo.sysattributes '
1381                  + 'where class=1 and attribute in (1650,1651,1652,1653)')
1382  
1383              /* Drop role sa_serverprivs_role */
1384              insert into #downgrade_esd values (1, 20,
1385                  'drop role sa_serverprivs_role with override')
1386  
1387              /* Delete all new GP privileges from sysprotects */
1388              insert into #downgrade_esd values (@dbid, 20,
1389                  'delete ' + @dbname + '.dbo.sysprotects '
1390                  + 'from ' + @dbname + '.dbo.sysprotects p,master..spt_values v '
1391                  + 'where p.action=v.number and '
1392                  + 'v.type=''GP''')
1393  
1394              /* 
1395              ** Delete config options 
1396              ** 'enable predicated privileges'
1397              ** 'enable granular permissions'
1398              ** 'enable async database init'
1399              ** 'enable concurrent dump tran'
1400              ** 'update statistics hashing',
1401              ** 'enable spinlock monitoring,
1402              ** 'memory dump compression level'
1403              ** 'enable dump history'
1404              ** 'dump history filename'
1405              ** 'recovery prefetch size'
1406              */
1407              insert into #downgrade_esd values (1, 20,
1408                  'delete master..sysconfigures where config in (525, 526, 564, 565, 570, 571, 572, 573, 574, 458)')
1409  
1410              /* End of PP and GP downgrade */
1411  
1412              /* Remove DUMP CONFIG options. */
1413              insert into #downgrade_esd values (1, 20,
1414                  'exec sp_downgrade_dump_config')
1415  
1416  
1417              insert into #downgrade_esd values (1, 20,
1418                  'if exists (select 1 from master..sysobjects where name = ''monSpinlockActivity'') '
1419                  + 'begin '
1420                  + 'drop table monSpinlockActivity '
1421                  + 'end ')
1422  
1423          end --} ESD1
1424  
1425          /*
1426          ** Fix up this database so that online will do an upgrade to
1427          ** put it back to what it should be.
1428          */
1429          /* 
1430          ** Downgrade the DBINFO->dbi_upgd_minor value. For simplicity,
1431          ** we all set the value same as ESD#1's, it will be updated
1432          ** later during the lower server booting.
1433          */
1434          if @orig_dbname != 'master'
1435          begin --{
1436              select @retval = dbinfo_update(@orig_dbname, 'upgd_minor', 1653)
1437              if @retval != 1
1438                  select @error_count = @error_count + 1
1439          end --}
1440          else
1441          begin --{
1442              select @retval = dbinfo_update(@orig_dbname, 'upgd_minor', 1718)
1443              if @retval != 1
1444                  select @error_count = @error_count + 1
1445          end --}
1446  
1447          select @retval = dbinfo_update(@orig_dbname, 'vers_tag', 32767)
1448          if @retval != 1
1449              select @error_count = @error_count + 1
1450  
1451          /*
1452          ** Version-specific checks passed. We can do the downgrade. Process
1453          ** items for each SP in decreasing order. We want to revert changes
1454          ** for the higher-numbered SPs first. While @sp_now is higher than
1455          ** @sp_tgt, decrement @sp_now to the next lower SP level and
1456          ** process its reversion steps. After processing steps applicable
1457          ** to @sp_tgt, stop.
1458          */
1459          while @sp_now > @sp_tgt
1460          begin
1461              declare downgrade_c cursor for
1462              select cmd from #downgrade_esd
1463              where dbid = @dbid and tgt = @sp_now
1464              open downgrade_c
1465              fetch downgrade_c into @sqlcmd
1466              while (@@sqlstatus = 0)
1467              begin
1468                  if (@verbose = 1)
1469                      print @sqlcmd
1470                  select @retval = 0
1471                  exec (@sqlcmd)
1472                  if (@retval != 0) or (@@error != 0)
1473                  begin
1474                      print 'Command failed with status %1!, error %2!: %3!',
1475                          @retval, @@error, @sqlcmd
1476                      select @error_count = @error_count + 1
1477                  end
1478                  fetch downgrade_c into @sqlcmd
1479              end
1480              close downgrade_c
1481              deallocate cursor downgrade_c
1482              select @sp_now = isnull(max(tgt), 0)
1483              from #downgrade_esd
1484              where tgt < @sp_now
1485          end
1486  
1487          -- We are now done with our SQL tables. Get rid of them.
1488          drop table #downgrade_esd
1489          if (@create_perm_tables is not null)
1490          begin
1491              drop table #users
1492              drop table #tables
1493              drop table #grantee
1494              drop table #perm
1495          end
1496  
1497          /*
1498          ** Drop sysprotects.csysprotects index as predid was added to index
1499          ** for predicated privileges
1500          */
1501          if (@sp_tgt < 20)
1502          begin --{
1503              print 'Dropping and recreating index on Sysprotects for database ''%1!''.',
1504                  @orig_dbname
1505  
1506              dbcc dbrepair(@orig_dbname, REPAIRINDEX, 'sysprotects', 2,
1507                  @downgrd_sysprot_index)
1508          end --}
1509  
1510          if @error_count != 0
1511          begin
1512              -- Something failed, so we fail.
1513              print 'One or more commands failed. Reversion is not complete.'
1514          end
1515          else
1516          begin
1517              -- We did all our work successfully.
1518              print 'Database ''%1!'' is now suitable for use by %2!.'
1519                  , @orig_dbname, @target
1520  
1521              select @nullarg = db_attr(@orig_dbname, 'list_dump_fs')
1522              print ' '
1523  
1524              select @success = 0
1525          end
1526  cleanup:
1527          /*
1528          ** If we turned updates on, turn them off again.
1529          */
1530          if (@update_val = 0)
1531          begin
1532              exec sp_configure 'allow updates to system tables', 0
1533          end
1534  
1535          return @success
1536      end -- }
1537  


exec sp_procxmode 'sp_downgrade_esd', 'AnyMode'
go

Grant Execute on sp_downgrade_esd to public
go
RESULT SETS
sp_downgrade_esd_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 97
 MEST 4 Empty String will be replaced by Single Space 508
 MEST 4 Empty String will be replaced by Single Space 510
 MEST 4 Empty String will be replaced by Single Space 546
 MEST 4 Empty String will be replaced by Single Space 548
 MEST 4 Empty String will be replaced by Single Space 569
 MEST 4 Empty String will be replaced by Single Space 571
 MEST 4 Empty String will be replaced by Single Space 573
 MEST 4 Empty String will be replaced by Single Space 594
 MEST 4 Empty String will be replaced by Single Space 633
 MEST 4 Empty String will be replaced by Single Space 654
 MEST 4 Empty String will be replaced by Single Space 657
 MEST 4 Empty String will be replaced by Single Space 659
 MEST 4 Empty String will be replaced by Single Space 680
 MEST 4 Empty String will be replaced by Single Space 682
 MEST 4 Empty String will be replaced by Single Space 779
 MEST 4 Empty String will be replaced by Single Space 1127
 MEST 4 Empty String will be replaced by Single Space 1146
 MEST 4 Empty String will be replaced by Single Space 1149
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 1151
 QCSC 4 Costly 'select count()', use 'exists()' 1110
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
749
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 260
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 265
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 280
 QTYP 4 Comparison type mismatch smallint = int 280
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 610
 QTYP 4 Comparison type mismatch smallint = int 610
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1117
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1119
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 1121
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1133
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1134
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 1143
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 VRUN 4 Variable is read and not initialized @retval 348
 VRUN 4 Variable is read and not initialized @pgcount 477
 VRUN 4 Variable is read and not initialized @maxlen 491
 VRUN 4 Variable is read and not initialized @c 530
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause downgrade_c 1462
 MAW1 3 Warning message on %name% tempdb..#perm.id: Warning message on #perm_crby_sybsystemprocs__sp_downgrade_esd 1126
 MAW1 3 Warning message on %name% tempdb..#perm.id: Warning message on #perm_crby_sybsystemprocs__sp_downgrade_esd 1128
 MAW1 3 Warning message on %name% tempdb..#perm.id: Warning message on #perm_crby_sybsystemprocs__sp_downgrade_esd 1131
 MAW1 3 Warning message on %name% tempdb..#perm.id: Warning message on #perm_crby_sybsystemprocs__sp_downgrade_esd 1137
 MAW1 3 Warning message on %name% tempdb..#tables.id: Warning message on #tables_crby_sybsystemprocs__sp_downgrade_esd 1137
 MAW1 3 Warning message on %name% tempdb..#perm.id: Warning message on #perm_crby_sybsystemprocs__sp_downgrade_esd 1138
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 32
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysprocesses  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_downgrade_esd  
 MNER 3 No Error Check should check return value of exec 122
 MNER 3 No Error Check should check return value of exec 807
 MNER 3 No Error Check should check return value of exec 818
 MNER 3 No Error Check should check return value of exec 824
 MNER 3 No Error Check should check @@error after insert 848
 MNER 3 No Error Check should check @@error after insert 863
 MNER 3 No Error Check should check @@error after insert 880
 MNER 3 No Error Check should check @@error after insert 885
 MNER 3 No Error Check should check @@error after insert 896
 MNER 3 No Error Check should check @@error after insert 907
 MNER 3 No Error Check should check @@error after insert 916
 MNER 3 No Error Check should check @@error after insert 920
 MNER 3 No Error Check should check @@error after insert 931
 MNER 3 No Error Check should check @@error after insert 938
 MNER 3 No Error Check should check @@error after insert 945
 MNER 3 No Error Check should check @@error after insert 952
 MNER 3 No Error Check should check @@error after insert 956
 MNER 3 No Error Check should check @@error after insert 964
 MNER 3 No Error Check should check @@error after insert 984
 MNER 3 No Error Check should check @@error after insert 993
 MNER 3 No Error Check should check @@error after insert 1000
 MNER 3 No Error Check should check @@error after select into 1113
 MNER 3 No Error Check should check return value of exec 1151
 MNER 3 No Error Check should check @@error after insert 1268
 MNER 3 No Error Check should check @@error after insert 1277
 MNER 3 No Error Check should check @@error after insert 1280
 MNER 3 No Error Check should check @@error after insert 1283
 MNER 3 No Error Check should check @@error after insert 1286
 MNER 3 No Error Check should check @@error after insert 1289
 MNER 3 No Error Check should check @@error after insert 1293
 MNER 3 No Error Check should check @@error after insert 1302
 MNER 3 No Error Check should check @@error after insert 1307
 MNER 3 No Error Check should check @@error after insert 1312
 MNER 3 No Error Check should check @@error after insert 1320
 MNER 3 No Error Check should check @@error after insert 1328
 MNER 3 No Error Check should check @@error after insert 1336
 MNER 3 No Error Check should check @@error after insert 1344
 MNER 3 No Error Check should check @@error after insert 1353
 MNER 3 No Error Check should check @@error after insert 1359
 MNER 3 No Error Check should check @@error after insert 1365
 MNER 3 No Error Check should check @@error after insert 1370
 MNER 3 No Error Check should check @@error after insert 1374
 MNER 3 No Error Check should check @@error after insert 1379
 MNER 3 No Error Check should check @@error after insert 1384
 MNER 3 No Error Check should check @@error after insert 1388
 MNER 3 No Error Check should check @@error after insert 1407
 MNER 3 No Error Check should check @@error after insert 1413
 MNER 3 No Error Check should check @@error after insert 1417
 MNER 3 No Error Check should check return value of exec 1532
 MUCO 3 Useless Code Useless Begin-End Pair 37
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 220
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 317
 MUCO 3 Useless Code Useless Brackets 332
 MUCO 3 Useless Code Useless Brackets 348
 MUCO 3 Useless Code Useless Brackets 370
 MUCO 3 Useless Code Useless Brackets 420
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 430
 MUCO 3 Useless Code Useless Brackets 438
 MUCO 3 Useless Code Useless Brackets 453
 MUCO 3 Useless Code Useless Brackets 461
 MUCO 3 Useless Code Useless Brackets 468
 MUCO 3 Useless Code Useless Brackets 477
 MUCO 3 Useless Code Useless Brackets 521
 MUCO 3 Useless Code Useless Brackets 562
 MUCO 3 Useless Code Useless Brackets 564
 MUCO 3 Useless Code Useless Brackets 584
 MUCO 3 Useless Code Useless Brackets 603
 MUCO 3 Useless Code Useless Brackets 611
 MUCO 3 Useless Code Useless Brackets 693
 MUCO 3 Useless Code Useless Brackets 727
 MUCO 3 Useless Code Useless Brackets 822
 MUCO 3 Useless Code Useless Brackets 845
 MUCO 3 Useless Code Useless Brackets 854
 MUCO 3 Useless Code Useless Brackets 875
 MUCO 3 Useless Code Useless Brackets 1005
 MUCO 3 Useless Code Useless Brackets 1013
 MUCO 3 Useless Code Useless Brackets 1021
 MUCO 3 Useless Code Useless Brackets 1089
 MUCO 3 Useless Code Useless Brackets 1110
 MUCO 3 Useless Code Useless Brackets 1128
 MUCO 3 Useless Code Useless Brackets 1133
 MUCO 3 Useless Code Useless Brackets 1164
 MUCO 3 Useless Code Useless Brackets 1261
 MUCO 3 Useless Code Useless Brackets 1466
 MUCO 3 Useless Code Useless Brackets 1468
 MUCO 3 Useless Code Useless Brackets 1489
 MUCO 3 Useless Code Useless Brackets 1501
 MUCO 3 Useless Code Useless Brackets 1530
 MUIN 3 Column created using implicit nullability 839
 MUIN 3 Column created using implicit nullability 1031
 MUIN 3 Column created using implicit nullability 1034
 MUIN 3 Column created using implicit nullability 1037
 MUIN 3 Column created using implicit nullability 1043
 MZMB 3 Zombie: use of non-existent object select @c = value from master.dbo.sysconfigures where config=587 0
 QAFM 3 Var Assignment from potentially many rows 263
 QAFM 3 Var Assignment from potentially many rows 747
 QAFM 3 Var Assignment from potentially many rows 802
 QCRS 3 Conditional Result Set 756
 QCTC 3 Conditional Table Creation 1031
 QCTC 3 Conditional Table Creation 1034
 QCTC 3 Conditional Table Creation 1037
 QCTC 3 Conditional Table Creation 1043
 QCTC 3 Conditional Table Creation 1113
 QCTC 3 Conditional Table Creation 1207
 QCTC 3 Conditional Table Creation 1222
 QDIS 3 Check correct use of 'select distinct' 1113
 QGWO 3 Group by/Distinct/Union without order by 1113
 QJWT 3 Join or Sarg Without Index on temp table 1137
 QJWT 3 Join or Sarg Without Index on temp table 1144
 QNAJ 3 Not using ANSI Inner Join 1141
 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}
260
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
610
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
804
 VNRD 3 Variable is not read @gp_enabled 123
 VNRD 3 Variable is not read @dummy 126
 VNRD 3 Variable is not read @sp_str 199
 VNRD 3 Variable is not read @nullarg 1521
 VUNU 3 Variable is not used @upgd_minor 49
 VUNU 3 Variable is not used @dbi_logrecstat 73
 CUPD 2 Updatable Cursor Marker (updatable by default) 1462
 MDYE 2 Dynamic Exec Marker exec @retval 423
 MDYE 2 Dynamic Exec Marker exec @sqlcmd 592
 MDYS 2 Dynamic SQL Marker 347
 MDYS 2 Dynamic SQL Marker 352
 MDYS 2 Dynamic SQL Marker 357
 MDYS 2 Dynamic SQL Marker 369
 MDYS 2 Dynamic SQL Marker 437
 MDYS 2 Dynamic SQL Marker 443
 MDYS 2 Dynamic SQL Marker 448
 MDYS 2 Dynamic SQL Marker 460
 MDYS 2 Dynamic SQL Marker 476
 MDYS 2 Dynamic SQL Marker 489
 MDYS 2 Dynamic SQL Marker 498
 MDYS 2 Dynamic SQL Marker 509
 MDYS 2 Dynamic SQL Marker 529
 MDYS 2 Dynamic SQL Marker 539
 MDYS 2 Dynamic SQL Marker 547
 MDYS 2 Dynamic SQL Marker 561
 MDYS 2 Dynamic SQL Marker 583
 MDYS 2 Dynamic SQL Marker 623
 MDYS 2 Dynamic SQL Marker 632
 MDYS 2 Dynamic SQL Marker 643
 MDYS 2 Dynamic SQL Marker 655
 MDYS 2 Dynamic SQL Marker 668
 MDYS 2 Dynamic SQL Marker 681
 MDYS 2 Dynamic SQL Marker 692
 MDYS 2 Dynamic SQL Marker 726
 MDYS 2 Dynamic SQL Marker 731
 MDYS 2 Dynamic SQL Marker 736
 MDYS 2 Dynamic SQL Marker 769
 MDYS 2 Dynamic SQL Marker 778
 MDYS 2 Dynamic SQL Marker 1058
 MDYS 2 Dynamic SQL Marker 1064
 MDYS 2 Dynamic SQL Marker 1074
 MDYS 2 Dynamic SQL Marker 1087
 MDYS 2 Dynamic SQL Marker 1107
 MDYS 2 Dynamic SQL Marker 1173
 MDYS 2 Dynamic SQL Marker 1185
 MDYS 2 Dynamic SQL Marker 1200
 MDYS 2 Dynamic SQL Marker 1216
 MDYS 2 Dynamic SQL Marker 1232
 MDYS 2 Dynamic SQL Marker 1471
 MRST 2 Result Set Marker 756
 MSUB 2 Subquery Marker 243
 MSUB 2 Subquery Marker 259
 MSUB 2 Subquery Marker 279
 MSUB 2 Subquery Marker 751
 MSUC 2 Correlated Subquery Marker 1129
 MSUC 2 Correlated Subquery Marker 1135
 MTR1 2 Metrics: Comments Ratio Comments: 33% 32
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 85 = 94dec - 11exi + 2 32
 MTR3 2 Metrics: Query Complexity Complexity: 574 32

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#perm (1) 
read_writes table tempdb..#users (1) 
writes table sybsystemprocs..sp_downgrade_esd_rset_001 
writes table tempdb..#grantee (1) 
reads table master..sysprocesses (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_downgrade_sysdams  
   reads table master..sysdatabases (1)  
   reads table master..spt_values (1)  
read_writes table tempdb..#downgrade_esd (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   reads table tempdb..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..systypes (1)  
   reads table master..syscolumns (1)  
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   calls proc sybsystemprocs..sp_autoformat  
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   writes table sybsystemprocs..sp_autoformat_rset_002 
reads table master..sysconfigures (1)  
read_writes table tempdb..#perm_dbcc_t_tab (1) 
calls proc sybsystemprocs..sp_configure  
   reads table master..syscharsets (1)  
   read_writes table tempdb..#configure_temp (1) 
   reads table master..sysdevices (1)  
   writes table sybsystemprocs..sp_configure_rset_004 
   calls proc sybsystemprocs..sp_configure  
   reads table master..sysattributes (1)  
   writes table tempdb..#temptab (1) 
   reads table master..spt_values (1)  
   calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_configure_rset_005 
   read_writes table tempdb..#optlevel (1) 
   reads table master..syslanguages (1)  
   reads table master..sysconfigures (1)  
   writes table tempdb..#temptable3 (1) 
   calls proc sybsystemprocs..sp_aux_checkroleperm  
   writes table tempdb..#temptable (1) 
   writes table sybsystemprocs..sp_configure_rset_006 
   writes table sybsystemprocs..sp_configure_rset_001 
   writes table tempdb..#temptable1 (1) 
   calls proc sybsystemprocs..sp_getmessage  
   writes table sybsystemprocs..sp_configure_rset_002 
   calls proc sybsystemprocs..sp_aux_getsize  
   reads table master..syscurconfigs (1)  
   writes table sybsystemprocs..sp_configure_rset_003 
reads table master..sysdatabases (1)  
reads table master..sysattributes (1)  
reads table master..sysusages (1)  
read_writes table tempdb..#tables (1) 
writes table tempdb..#permissions (1) 
read_writes table tempdb..#perm_bi_t_tab (1) 
reads table master..spt_values (1)