DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_runcmd  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Tue Oct 31 23:35:58 2006 
4     */
5     /*
6     ** raiserror Messages for spaceusage_runcmd [Total 0]
7     */
8     /*
9     ** sp_getmessage Messages for spaceusage_runcmd [Total 1]
10    **
11    ** 19538, "Command '%1!' successful."
12    */
13    /*
14    ** End spgenmsgs.pl output.
15    */
16    /*
17    **	SP_SPACEUSAGE_RUNCMD
18    **	
19    **	Internal [only] stored procedure that will run the command passed on all
20    **	the tables that match the input pattern for table name and owned by user
21    **	name that match the input pattern for onwer name.
22    **
23    **	Parameters
24    **		@uname	- The owner name pattern string.
25    **		@tname	- The table name pattern string.
26    **		@command- The command to be run.
27    **		@verbose- If set, will print user information that the command
28    **			  was run successfully on the object.
29    **	
30    **	Returns
31    **		0 - if all goes well.
32    **	    other - error during execution.
33    {
34    */
35    create procedure sp_spaceusage_runcmd
36    (
37        @uname varchar(255)
38        , @tname varchar(255)
39        , @command varchar(100)
40        , @verbose bit = 0
41    )
42    as
43        begin -- { 	-- procedure begins! 
44    
45            declare @objname varchar(255)
46                , @ownername varchar(30)
47                , @sysstat int
48                , @sysstat2 int
49                , @sqlstmt varchar(400)
50                , @retvalue int
51                , @whoami varchar(50)
52                , @msg varchar(256)
53    
54            select @whoami = "sp_spaceusage_runcmd"
55    
56            declare objname_cur cursor for
57            select name, user_name(uid), sysstat, sysstat2
58            from sysobjects
59            where user_name(uid) like @uname
60    
61                and name like @tname
62    
63                -- Eliminate some catalogs on which we know that
64                -- certain commands cannot be run. Otherwise, we'll
65                -- get unnecessary errors from UPDATE * STATISTICS
66                -- command on this table.
67                --
68                and name NOT IN ('syslogs', 'sysgams')
69    
70                -- Eliminate fake catalogs (e.g. sysprocesses, syslocks
71                -- and such like). 
72                --
73                and ((sysstat & 1024) != 1024)
74    
75                -- Eliminate proxy tables 
76                and ((sysstat2 & 1024) != 1024)
77    
78                -- Eliminate any views or other objects that user 
79                -- might have selected by "%" specifier.
80                --
81                and type IN ('U', 'S')
82    
83            open objname_cur
84    
85            while (1 = 1)
86            begin -- {
87    
88                fetch objname_cur into @objname, @ownername, @sysstat, @sysstat2
89    
90                if (@@sqlstatus != 0)
91                    break
92    
93                select @sqlstmt = @command + " " + @ownername + "." + @objname
94    
95                exec @retvalue = sp_exec_SQL @sqlstmt, @whoami
96    
97                if @retvalue != 0
98                begin
99                    close objname_cur
100                   deallocate cursor objname_cur
101                   return (@retvalue)
102               end
103   
104               if @verbose = 1
105               begin
106                   exec sp_getmessage 19538, @msg out
107                   print @msg, @sqlstmt
108               end
109   
110           end -- }	-- End of while
111   
112           close objname_cur
113   
114           deallocate cursor objname_cur
115   
116       end -- } 	-- }
117   


exec sp_procxmode 'sp_spaceusage_runcmd', 'AnyMode'
go

Grant Execute on sp_spaceusage_runcmd to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @callerID: varchar(30) = varchar(50) 95
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause objname_cur 57
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_runcmd  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 106
 MUCO 3 Useless Code Useless Brackets in create proc 36
 MUCO 3 Useless Code Useless Begin-End Pair 43
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 101
 CUPD 2 Updatable Cursor Marker (updatable by default) 57
 MTR1 2 Metrics: Comments Ratio Comments: 44% 35
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 9dec - 2exi + 2 35
 MTR3 2 Metrics: Query Complexity Complexity: 31 35

DEPENDENCIES
PROCS AND TABLES USED
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)  
calls proc sybsystemprocs..sp_exec_SQL  
reads table sybsystemprocs..sysobjects  

CALLERS
called by proc sybsystemprocs..sp_spaceusage