DatabaseProcApplicationCreatedLinks
sybsystemprocssp_lookup  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/lock */
4     
5     create procedure sp_lookup
6         @typearg int,
7         @owname varchar(255) = "%", /* owner of the desired objects (except indexes) */
8         @obname varchar(255) = "%", /* name of the desired objects (including indexes) */
9         @indtab varchar(255) = "%", /* table associated with the desired index */
10        @indown varchar(255) = "%" /* owner of the desired index */
11    as
12        create table #ddtab
13        (name varchar(255),
14            owner varchar(255),
15            type int,
16            indtab varchar(255))
17    
18        declare @table int
19        declare @view int
20        declare @index int
21        declare @sproc int
22        declare @trgr int
23        declare @dflt int
24        declare @rule int
25        declare @dtype int
26        declare @db int
27        declare @report int
28    
29    
30    
31        if @@trancount = 0
32        begin
33            set chained off
34        end
35    
36        set transaction isolation level 1
37    
38        select @table = 1, @view = 2, @index = 4, @sproc = 8, @trgr = 16, @dflt = 32,
39            @rule = 64, @dtype = 128, @db = 256, @report = 512
40    
41        if (@typearg & @table = @table)
42            insert into #ddtab
43            select name, user_name(uid), @table, '' from dbo.sysobjects
44            where name like @obname
45                and user_name(uid) like @owname
46                and (sysstat & 7 = 1 /* system table */
47                    or sysstat & 7 = 3) /* user table */
48    
49    
50        if (@typearg & @view = @view)
51            insert into #ddtab
52            select name, user_name(uid), @view, '' from dbo.sysobjects
53            where name like @obname
54                and user_name(uid) like @owname
55                and sysstat & 7 = 2 /* view */
56    
57        if (@typearg & @index = @index)
58            insert into #ddtab
59            select i.name, user_name(o.uid), @index, o.name
60            from dbo.sysindexes i, dbo.sysobjects o
61            where i.name like @obname
62                and i.indid > 0
63                and i.id = o.id
64                and user_name(o.uid) like @indown
65                and o.name = object_name(o.id)
66                and o.name like @indtab
67    
68        if (@typearg & @report = @report)
69            insert into #ddtab
70            select name, user_name(uid), @report, '' from dbo.sysobjects
71            where name like @obname
72                and user_name(uid) like @owname
73                and sysstat & 7 = 4 /* sproc */
74                and userstat & - 32768 = - 32768 /* report */
75    
76        if (@typearg & @sproc = @sproc)
77        begin
78            /*
79            **  If we also wanted reports, don't pick up reports again as sprocs.
80            */
81            if (@typearg & @report = @report)
82            begin
83                insert into #ddtab
84                select name, user_name(uid), @sproc, '' from dbo.sysobjects
85                where name like @obname
86                    and user_name(uid) like @owname
87                    and sysstat & 7 = 4 /* stored procedure */
88                    and userstat & - 32768 != - 32768 /* report */
89            end
90            else
91            begin
92                insert into #ddtab
93                select name, user_name(uid), @sproc, '' from dbo.sysobjects
94                where name like @obname
95                    and user_name(uid) like @owname
96                    and sysstat & 7 = 4 /* stored procedure */
97            end
98        end
99    
100       if (@typearg & @trgr = @trgr)
101           insert into #ddtab
102           select name, user_name(uid), @trgr, '' from dbo.sysobjects
103           where name like @obname
104               and user_name(uid) like @owname
105               and sysstat & 7 = 0 /* trigger */
106   
107       if (@typearg & @dflt = @dflt)
108           insert into #ddtab
109           select name, user_name(uid), @dflt, '' from dbo.sysobjects
110           where name like @obname
111               and user_name(uid) like @owname
112               and sysstat & 7 = 6 /* default */
113   
114       if (@typearg & @rule = @rule)
115           insert into #ddtab
116           select name, user_name(uid), @rule, '' from dbo.sysobjects
117           where name like @obname
118               and user_name(uid) like @owname
119               and sysstat & 7 = 7 /* rule */
120   
121       if (@typearg & @dtype = @dtype)
122           insert into #ddtab
123           select name, user_name(uid), @dtype, '' from dbo.systypes
124           where name like @obname
125               and user_name(uid) like @owname
126   
127       if (@typearg & @db = @db)
128           insert into #ddtab
129           select name, suser_name(suid), @db, ''
130           from master.dbo.sysdatabases
131           where (name like @obname)
132               and suser_name(suid) like @owname
133   
134       exec sp_autoformat @fulltabname = #ddtab,
135           @orderby = "order by name"
136   
137       drop table #ddtab
138   
139       return (0)
140   


exec sp_procxmode 'sp_lookup', 'AnyMode'
go

Grant Execute on sp_lookup to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 43
 MEST 4 Empty String will be replaced by Single Space 52
 MEST 4 Empty String will be replaced by Single Space 70
 MEST 4 Empty String will be replaced by Single Space 84
 MEST 4 Empty String will be replaced by Single Space 93
 MEST 4 Empty String will be replaced by Single Space 102
 MEST 4 Empty String will be replaced by Single Space 109
 MEST 4 Empty String will be replaced by Single Space 116
 MEST 4 Empty String will be replaced by Single Space 123
 MEST 4 Empty String will be replaced by Single Space 129
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 134
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}		 61
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 62
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_lookup  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MNER 3 No Error Check should check @@error after insert 42
 MNER 3 No Error Check should check @@error after insert 51
 MNER 3 No Error Check should check @@error after insert 58
 MNER 3 No Error Check should check @@error after insert 69
 MNER 3 No Error Check should check @@error after insert 83
 MNER 3 No Error Check should check @@error after insert 92
 MNER 3 No Error Check should check @@error after insert 101
 MNER 3 No Error Check should check @@error after insert 108
 MNER 3 No Error Check should check @@error after insert 115
 MNER 3 No Error Check should check @@error after insert 122
 MNER 3 No Error Check should check @@error after insert 128
 MNER 3 No Error Check should check return value of exec 134
 MUCO 3 Useless Code Useless Brackets 41
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 139
 MUIN 3 Column created using implicit nullability 12
 QISO 3 Set isolation level 36
 QNAJ 3 Not using ANSI Inner Join 60
 MTR1 2 Metrics: Comments Ratio Comments: 12% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 12dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 114 5
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 59

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