DatabaseProcApplicationCreatedLinks
sybsystemprocssp_opt_querystats  31 Aug 14Defects Dependencies

1     
2     
3     /*
4     ** SP_OPT_QUERYSTATS
5     **
6     ** This stored procedure is a wrapper for the sp_opt_querystats_main
7     ** stored procedure.  sp_opt_querystats_main contains references to
8     ** objects in the sybmgmtdb database and cannot be executed unless that
9     ** database exists and the Job Scheduler has been installed.  This
10    ** stored procedure is used to verify that sybmgmtdb has been installed
11    ** before sp_opt_querystats_main is invoked.
12    **
13    ** History:
14    **   4/28/2011    pdorfman   written
15    */
16    create procedure sp_opt_querystats
17        @query varchar(16384),
18        @input_params varchar(2048) = "all",
19        @target_database varchar(30) = null,
20        @user_name varchar(30) = null
21    as
22        declare @ret int
23        declare @cmd varchar(1024)
24        declare @js_installed int
25    
26        /*
27        ** First check to see whether the sybmgmtdb database exists
28        */
29        if not exists (select 1 from master.dbo.sysdatabases where name = "sybmgmtdb")
30        begin
31            /*
32            ** "ERROR: The Job Scheduler database could not be found on this server.
33            ** The Job Scheduler must be installed in order to use this stored procedure."
34            */
35            raiserror 17007
36            return - 1
37        end
38    
39        /*
40        ** If sybmgmtdb exists, verify that one of the Job Scheduler stored
41        ** procedures is installed. This must be executed using execute immediate
42        ** because otherwise this statement will fail to compile and prevent this
43        ** procedure from compiling if the sybmgmtdb database does not exist.
44        ** That would defeat the whole purpose of this test.
45        */
46        select @cmd =
47            "if exists (select 1 from sybmgmtdb.dbo.sysobjects
48    	where name = 'sp_sjobhistory')
49    begin
50      select @js_installed = 1
51    end
52    else
53    begin
54      select @js_installed = 0
55    end"
56    
57        exec (@cmd)
58    
59        if (@js_installed = 0)
60        begin
61            /*
62            ** "ERROR: The Job Scheduler is not installed on this server. The Job
63            **  Scheduler must be installed in order to use this stored procedure."
64            */
65            raiserror 17008
66            return - 1
67        end
68    
69        /*
70        ** It's OK to execute the main stored procedure
71        */
72        exec @ret = sp_opt_querystats_main @query, @input_params, @target_database,
73            @user_name
74    
75        return @ret
76    
77    


exec sp_procxmode 'sp_opt_querystats', 'AnyMode'
go

Grant Execute on sp_opt_querystats to public
go
DEFECTS
 MBRV 4 Bad return value [-1-99] return - 1 36
 MBRV 4 Bad return value [-1-99] return - 1 66
 VRUN 4 Variable is read and not initialized @js_installed 59
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 16
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_opt_querystats  
 MNER 3 No Error Check should check return value of exec 72
 MUCO 3 Useless Code Useless Brackets 59
 MDYS 2 Dynamic SQL Marker 57
 MSUB 2 Subquery Marker 29
 MTR1 2 Metrics: Comments Ratio Comments: 60% 16
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 1 = 2dec - 3exi + 2 16
 MTR3 2 Metrics: Query Complexity Complexity: 18 16

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_opt_querystats_main  
   reads table master..sysprocesses (1)  
   reads table master..sysconfigures (1)  
   reads table master..sysdatabases (1)  
   read_writes table tempdb..#temp_history (1) 
   reads table tempdb..sysobjects (1)  
   reads table master..syscurconfigs (1)  
reads table master..sysdatabases (1)