DatabaseProcApplicationCreatedLinks
sybsystemprocssp_recompile  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Tue May 31 02:59:14 2005 
4     */
5     /*
6     ** raiserror Messages for /calm/svr/sql/generic/sproc/recompile [Total 7]
7     **
8     ** 17390, "Table or view name must be in current database."
9     ** 17460, "Object must be in the current database."
10    ** 17732, "You do not own table '%1!'."
11    ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record."
12    ** 17760, "%1!' is a system table. Cannot use '%2!' on system tables."
13    ** 17761, "Object '%1!' is not a table."
14    ** 17763, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in updating the schemacnt column in sysobjects."
15    */
16    /*
17    ** sp_getmessage Messages for /calm/svr/sql/generic/sproc/recompile [Total 1]
18    **
19    ** 17762, "Each stored procedure and trigger that uses table '%1!' will be recompiled the next time it is executed."
20    */
21    /*
22    ** End spgenmsgs.pl output.
23    */
24    /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
25    /*	4.8	1.1	06/14/90	sproc/src/password */
26    
27    /*
28    ** IMPORTANT NOTE:
29    ** This stored procedure uses the built-in function object_id() in the
30    ** where clause of a select query. If you intend to change this query
31    ** or use the object_id() or db_id() builtin in this procedure, please read the
32    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
33    ** pertaining to object-id's and db-id's outlined there, are followed.
34    */
35    
36    create procedure sp_recompile
37        @objname varchar(767)
38    as
39    
40        declare @type smallint
41        declare @uid int
42        declare @msg varchar(1024)
43        declare @dbname varchar(255)
44    
45    
46        if @@trancount = 0
47        begin
48            set chained off
49        end
50    
51        set transaction isolation level 1
52    
53        if @objname like '%%.%%.%%' and
54            substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
55        begin
56            /*
57            ** 17390, "Table or view name must be in 'current' database."
58            */
59            raiserror 17390
60            return (1)
61        end
62    
63        if not exists (select *
64                from sysobjects
65                where id = object_id(@objname))
66        begin
67            /*
68            ** 17460, "Object must be in the current database."
69            */
70            raiserror 17460
71            return (1)
72        end
73    
74        select @type = sysstat & 7, @uid = uid
75        from sysobjects
76        where id = object_id(@objname)
77        if @type = 1
78        begin
79            /*
80            ** 17760, "'%1!' is a system table.  Sp_recompile cannot be used on system tables."
81            */
82            raiserror 17760, @objname, "sp_recompile"
83            return (1)
84        end
85        if @type != 3
86        begin
87            /*
88            ** 17761, "Object '%1!' is not a table."
89            */
90            raiserror 17761, @objname
91            return (1)
92        end
93    
94        if user_id() != 1 and @uid != user_id()
95        begin
96            /*
97            ** 17732, "You do not own table '%1!'." 
98            */
99            raiserror 17732, @objname
100           return (1)
101       end
102   
103       set nocount on
104   
105       /* Start the transaction to log the execution of this procedure.
106       **
107       ** IMPORTANT: The name "rs_logexec is significant and is used by 
108       **            Replication Server
109       */
110       begin transaction rs_logexec
111   
112       /*
113       ** Increment the schemacnt associated with cached des.
114       */
115       if (schema_inc(object_id(@objname), 1) != 1)
116       begin
117           /*
118           ** 17763, "The execution of the stored procedure '%1!'
119           **         in database '%2!' was aborted because there
120           **         was an error in updating the column
121           **         schemacnt in sysobjects."
122           */
123           select @dbname = db_name()
124           raiserror 17763, "sp_recompile", @dbname
125   
126           rollback transaction rs_logexec
127           return (1)
128       end
129   
130       /*
131       ** Write the log record to replicate this invocation 
132       ** of the stored procedure.
133       */
134       if (logexec() != 1)
135       begin
136           /*
137           ** 17756, "The execution of the stored procedure '%1!'
138           ** 	   in database '%2!' was aborted because there
139           ** 	   was an error in writing the replication log
140           **	   record."
141           */
142           select @dbname = db_name()
143           raiserror 17756, "sp_recompile", @dbname
144   
145           rollback transaction rs_logexec
146           return (1)
147       end
148   
149       commit transaction
150   
151       /*
152       ** 17762, "Each stored procedure and trigger that uses table '%1!' will
153       **         be recompiled the next time it is executed."
154       */
155       exec sp_getmessage 17762, @msg output
156       print @msg, @objname
157   
158       return (0)
159   


exec sp_procxmode 'sp_recompile', 'AnyMode'
go

Grant Execute on sp_recompile to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_recompile  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 155
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 158
 QISO 3 Set isolation level 51
 MSUB 2 Subquery Marker 63
 MTR1 2 Metrics: Comments Ratio Comments: 62% 36
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 4 = 10dec - 8exi + 2 36
 MTR3 2 Metrics: Query Complexity Complexity: 54 36

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)