DatabaseProcApplicationCreatedLinks
sybsystemprocssp_aux_get_concrtlyownedobj  31 Aug 14Defects Dependencies

1     
2     create procedure sp_aux_get_concrtlyownedobj
3         @dbname varchar(30), /* database name */
4         @loginame varchar(255) /* login name */
5     as
6         declare @sqlj_proc int /* indicates a sqlj proc */
7             , @sptlang int
8         begin
9             select @sqlj_proc = hextoint("0x2000000")
10            select @sptlang = @@langid
11    
12            if @@langid != 0
13            begin
14                if not exists (
15                        select * from master.dbo.sysmessages where error
16                            between 17100 and 17109
17                            and langid = @@langid)
18                    select @sptlang = 0
19            end
20    
21            insert #object_info(Db_name, Object_name, Object_type, Object_owner, Object_loginame)
22            select @dbname
23                , o.name
24                , (case
25                    when ((o.sysstat & 15) = 8)
26                    then (case
27                        when (o.type = "IT")
28                        then "instead of "
29                        else null
30                    end)
31                    else null
32                end
33                )
34                + (m.description + x.name)
35                , user_name(o.uid)
36                , @loginame
37            from sysobjects o, master.dbo.spt_values v,
38                master.dbo.spt_values x, master.dbo.sysmessages m
39            /* 
40            ** The following where clause is used to get the correct
41            ** string names of object_type. In order to use the right
42            ** local language, all object type names except for 
43            ** REPROT TYPES are retrieved from sysmessages.description. 
44            ** "report types" is retrieved from spt_values.name.
45            **
46            ** Error msgs 17100 - 17199: names for basic objec types
47            ** E.g.,
48            **      17100: "trigger",
49            **      17101: "system table",
50            **      etc.
51            ** Error msg 17587: "partition condition",
52            **           17588: "encrypton key",
53            **           17589: "computed column",
54            **           18903: "SQLJ function", 
55            **           18904: "SQLJ procedure".
56            */
57            where o.loginame = @loginame
58                and o.sysstat & 2063 = v.number
59                and ((v.type = "O" and
60                        (o.type != "XP" and (o.sysstat2 & @sqlj_proc) = 0)) or
61                    (v.type = "O1" and o.type = "XP") or
62                    (v.type = "O2" and (o.sysstat2 & @sqlj_proc) != 0) or
63                    (v.type = "EK" and o.type = "EK"))
64                and v.msgnum = m.error
65                and isnull(m.langid, 0) = @sptlang
66                and ((m.error between 17100 and 17199) or
67                    (m.error between 17587 and 17589) or
68                    (m.error between 18903 and 18904))
69                /* Report Type */
70                and x.type = "R"
71                and o.userstat & - 32768 = x.number
72        end
73    


exec sp_procxmode 'sp_aux_get_concrtlyownedobj', 'AnyMode'
go

Grant Execute on sp_aux_get_concrtlyownedobj to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 17
 QTYP 4 Comparison type mismatch smallint = int 17
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_aux_get_concrtlyownedobj  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after insert 21
 MUCO 3 Useless Code Useless Begin-End Pair 8
 MUCO 3 Useless Code Useless Brackets 24
 MUCO 3 Useless Code Useless Brackets 25
 MUCO 3 Useless Code Useless Brackets 26
 MUCO 3 Useless Code Useless Brackets 27
 QNAJ 3 Not using ANSI Inner Join 37
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
15
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
64
 MSUB 2 Subquery Marker 14
 MTR1 2 Metrics: Comments Ratio Comments: 34% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 4dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 38 2
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, o=sybsystemprocs..sysobjects, sv=master..spt_values, sv2=master..spt_values} 0 22

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
writes table tempdb..#object_info (1) 
reads table master..spt_values (1)  
reads table master..sysmessages (1)