DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpextendedproc  31 Aug 14Defects Dependencies

1     
2     
3     create procedure sp_helpextendedproc
4         @esp_name varchar(255) = NULL /* esp name */
5     as
6     
7         declare @obj_id int
8         declare @maxlen int
9     
10        if @@trancount = 0
11        begin
12            set chained off
13        end
14    
15        set transaction isolation level 1
16    
17        set nocount on
18    
19        if @esp_name is NULL
20        begin
21            /*
22            **  If no @esp_name given, give a little info about all ESPs
23            **  in this database.  ESPs are indentified by the following
24            **  conditions:
25            **      1. The sysobjects row for the object must have a type
26            **         of `XP'
27            **      2. The object must be a stored procedure, which is
28            **         determined by the systat field
29            **         of the sysobjects-row for this object:
30            **              sysstat & 15  = 4       stored procedure
31            **
32            **  Note: 0x80f is the mask for sysstats (=2063decimal).
33            **        800 is used by Stratus for external tables.
34            */
35            select ESP_Name = o.name,
36                DLL_Name = getdllname(o.id)
37            into #helpexten1rs
38            from sysobjects o
39            where (o.sysstat & 15) = 4
40                and o.type = 'XP'
41                /* Call object_id() so that the access permissions
42                ** of the current user will be validated.
43                */
44                and o.id = object_id(user_name(o.uid) + '.' + o.name)
45            exec sp_autoformat @fulltabname = #helpexten1rs,
46                @selectlist = "'ESP NAME' = ESP_Name, 'DLL Name' = DLL_Name",
47                @orderby = "order by DLL_Name"
48            drop table #helpexten1rs
49        end
50        else
51        begin
52            /*
53            **  Make sure the @esp_name is local to the current database.
54            */
55            if @esp_name like "%.%.%"
56            begin
57                if substring(@esp_name, 1, charindex(".", @esp_name) - 1) != db_name()
58                begin
59                    /* 17460, "Object must be in the current database." */
60                    raiserror 17460
61                    return (1)
62                end
63            end
64    
65    
66            /* check that esp_name is a valid identifier */
67            select @maxlen = length from syscolumns
68            where id = object_id("sysobjects") and name = "name"
69            if valid_name(@esp_name, @maxlen) = 0
70            begin
71                /*
72                ** 17240, "'%1!' is not a valid name."
73                */
74                raiserror 17240, @esp_name
75                return (1)
76            end
77    
78            if not exists (select *
79                    from sysobjects
80                    where id = object_id(@esp_name))
81            begin
82                /*
83                ** The extended stored procedure '%1!' is not in sysobjects for this user.
84                */
85                raiserror 18391, @esp_name
86                return (1)
87            end
88    
89            select @obj_id = object_id(@esp_name)
90    
91            select ESP_Name = o.name,
92                DLL_Name = getdllname(o.id)
93            into #helpexten2rs
94            from sysobjects o
95            where (o.sysstat & 15) = 4
96                and o.type = 'XP'
97                and o.id = object_id(@esp_name)
98            exec sp_autoformat @fulltabname = #helpexten2rs,
99                @selectlist = "'ESP Name' = ESP_Name, 'DLL Name' = DLL_Name"
100           drop table #helpexten2rs
101   
102       end
103   
104       return (0)
105   


exec sp_procxmode 'sp_helpextendedproc', 'AnyMode'
go

Grant Execute on sp_helpextendedproc to public
go
DEFECTS
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 45
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 98
 MGTP 3 Grant to public sybsystemprocs..sp_helpextendedproc  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after select into 35
 MNER 3 No Error Check should check return value of exec 45
 MNER 3 No Error Check should check @@error after select into 91
 MNER 3 No Error Check should check return value of exec 98
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 104
 QAFM 3 Var Assignment from potentially many rows 67
 QCTC 3 Conditional Table Creation 35
 QCTC 3 Conditional Table Creation 91
 QISO 3 Set isolation level 15
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
68
 VNRD 3 Variable is not read @obj_id 89
 MSUB 2 Subquery Marker 78
 MTR1 2 Metrics: Comments Ratio Comments: 38% 3
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 11dec - 4exi + 2 3
 MTR3 2 Metrics: Query Complexity Complexity: 48 3

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..syscolumns  
calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_namecrack  
writes table tempdb..#helpexten1rs (1) 
writes table tempdb..#helpexten2rs (1)