DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_object  31 Aug 14Defects Dependencies

1     
2     /*
3     ** This procedure is used to drop the objects. Main purpose of this procedure 
4     ** is to replace the "if" clause in the install scripts with a call to this 
5     ** procedure. 
6     **
7     ** Arguments:
8     **	@objname = Name of the object to be dropped.
9     **	@objtype = Type of the object (procedure, table, view, trigger,
10    **			instead of trigger,...etc)
11    **
12    ** Returns:
13    **	0	- Successful execution.
14    **	1	- If @objname IS NULL.
15    ** 
16    ** Usage:
17    **	sp_drop_object @objname, @objtype
18    **
19    */
20    
21    create procedure sp_drop_object
22        @objname varchar(255) = NULL,
23        @objtype varchar(30) = "procedure"
24    as
25    
26        declare @type1 char(2)
27        /*
28        ** Check whether the procedure @objname is already exists.
29        ** If exists drop the procedure
30        */
31    
32        if (@objname IS NULL)
33        begin
34            raiserror 18001
35            return (1)
36        end
37        else
38        begin
39            select @type1 = type from sysobjects where name = @objname
40                and @objtype = "trigger"
41    
42            if exists (select 1 from sysobjects
43                    where name = @objname
44                        and type = case @objtype
45                            when "default" then 'D'
46                            when "function" then 'F'
47                            when "procedure" then 'P'
48                            when "table" then 'U'
49                            when "trigger" then @type1
50                            when "view" then 'V'
51                            else 'P'
52                        end)
53            begin
54                print "Dropping %1! %2!", @objtype, @objname
55    
56                /*
57                ** exec drop procedure 'proc_name'
58                */
59                exec ("drop " + @objtype + " " + @objname)
60            end
61        end
62    


exec sp_procxmode 'sp_drop_object', 'AnyMode'
go

Grant Execute on sp_drop_object to public
go
DEFECTS
 MPSI 4 Possible SQL Injection @objtype 59
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 21
 MGTP 3 Grant to public sybsystemprocs..sp_drop_object  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MUCO 3 Useless Code Useless Brackets 32
 MUCO 3 Useless Code Useless Brackets 35
 QAFM 3 Var Assignment from potentially many rows 39
 QPNC 3 No column in condition 40
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
39
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
43
 MDYS 2 Dynamic SQL Marker 59
 MSUB 2 Subquery Marker 42
 MTR1 2 Metrics: Comments Ratio Comments: 39% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 4 = 4dec - 2exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 17 21

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects