DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpjoins  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/help */
4     /*
5     ** Messages for "sp_helpjoins"          17650
6     **
7     ** 17460, "Object must be in the current database."
8     ** 17650, "First table doesn't exist."
9     ** 17651, "Second table doesn't exist."
10    ** 17652, "Object must be in your current database."
11    */
12    
13    /*
14    ** IMPORTANT NOTE:
15    ** This stored procedure uses the built-in function object_id() in the
16    ** where clause of a select query. If you intend to change this query
17    ** or use the object_id() or db_id() builtin in this procedure, please read the
18    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
19    ** pertaining to object-id's and db-id's outlined there, are followed.
20    */
21    
22    create procedure sp_helpjoins
23        @lefttab varchar(767), /* name of first table to join */
24        @righttab varchar(767) /* name of first table to join */
25    as
26    
27        declare @foundit bit /* flag to indicate joins found */
28    
29    
30        if @@trancount = 0
31        begin
32            set chained off
33        end
34    
35        set transaction isolation level 1
36    
37        /*
38        **  This procedure returns any foreign or common joins.
39        **  If none exists, then it returns any joins that have the same usertype.
40        **  If none exists, it returns any joins that have the same physical type.
41        */
42    
43        /*
44        **  Make sure the @objname is local to the current database.
45        */
46        if @lefttab like "%.%.%" and
47            substring(@lefttab, 1, charindex(".", @lefttab) - 1) != db_name()
48        begin
49            /* 17460, "Object must be in the current database." */
50            raiserror 17460
51            return (1)
52        end
53        if @righttab like "%.%.%" and
54            substring(@righttab, 1, charindex(".", @righttab) - 1) != db_name()
55        begin
56            /* 17460, "Object must be in the current database." */
57            raiserror 17460
58            return (1)
59        end
60    
61        /*
62        **  See if we can find the objects.  They must be a system table, user table,
63        **  or view.  The low 3 bits of sysobjects.sysstat indicate what the 
64        **  object type is -- it's more reliable than using sysobjects.type which
65        **  could change.
66        */
67        /*
68        **  If either of the tables don't exist, quit.
69        */
70        if not exists (select id
71                from sysobjects
72                where id = object_id(@lefttab)
73                    and (sysstat & 7 = 1 /* system table */
74                        or sysstat & 7 = 2 /* view */
75                        or sysstat & 7 = 3)) /* user table */
76        begin
77            /* 17650, "First table doesn't exist." */
78            raiserror 17650
79            return (1)
80        end
81    
82        if not exists (select id
83                from sysobjects
84                where id = object_id(@righttab)
85                    and (sysstat & 7 = 1 /* system table */
86                        or sysstat & 7 = 2 /* view */
87                        or sysstat & 7 = 3)) /* user table */
88    
89        begin
90            /* 17651, "Second table doesn't exist." */
91            raiserror 17651
92            return (1)
93        end
94    
95        create table #hjtab
96        (
97            a1 varchar(100),
98            a2 varchar(100),
99            b1 varchar(100) null,
100           b2 varchar(100) null,
101           c1 varchar(100) null,
102           c2 varchar(100) null,
103           d1 varchar(100) null,
104           d2 varchar(100) null,
105           e1 varchar(100) null,
106           e2 varchar(100) null,
107           f1 varchar(100) null,
108           f2 varchar(100) null,
109           g1 varchar(100) null,
110           g2 varchar(100) null,
111           h1 varchar(100) null,
112           h2 varchar(100) null
113       )
114   
115       /*
116       **  Look for foreign key joins.
117       */
118       select @foundit = 0
119       if exists (select *
120               from syskeys
121               where type = 2
122                   and id = object_id(@lefttab)
123                   and depid = object_id(@righttab))
124       begin
125           insert into #hjtab
126           select distinct first_pair = convert(varchar(100), col_name(id, key1)), convert(varchar(100), col_name(depid, depkey1)),
127               second_pair = convert(varchar(100), col_name(id, key2)), convert(varchar(100), col_name(depid, depkey2)),
128               third_pair = convert(varchar(100), col_name(id, key3)), convert(varchar(100), col_name(depid, depkey3)),
129               fourth_pair = convert(varchar(100), col_name(id, key4)), convert(varchar(100), col_name(depid, depkey4)),
130               fifth_pair = convert(varchar(100), col_name(id, key5)), convert(varchar(100), col_name(depid, depkey5)),
131               sixth_pair = convert(varchar(100), col_name(id, key6)), convert(varchar(100), col_name(depid, depkey6)),
132               seventh_pair = convert(varchar(100), col_name(id, key7)), convert(varchar(100), col_name(depid, depkey7)),
133               eighth_pair = convert(varchar(100), col_name(id, key8)), convert(varchar(100), col_name(depid, depkey8))
134           from syskeys
135           where type = 2
136               and id = object_id(@lefttab)
137               and depid = object_id(@righttab)
138   
139           select @foundit = 1
140       end
141   
142       if exists (select *
143               from syskeys
144               where type = 2
145                   and id = object_id(@righttab)
146                   and depid = object_id(@lefttab))
147       begin
148           insert into #hjtab
149           select distinct first_pair = convert(varchar(100), col_name(depid, depkey1)), convert(varchar(100), col_name(id, key1)),
150               second_pair = convert(varchar(100), col_name(depid, depkey2)), convert(varchar(100), col_name(id, key2)),
151               third_pair = convert(varchar(100), col_name(depid, depkey3)), convert(varchar(100), col_name(id, key3)),
152               fourth_pair = convert(varchar(100), col_name(depid, depkey4)), convert(varchar(100), col_name(id, key4)),
153               fifth_pair = convert(varchar(100), col_name(depid, depkey5)), convert(varchar(100), col_name(id, key5)),
154               sixth_pair = convert(varchar(100), col_name(depid, depkey6)), convert(varchar(100), col_name(id, key6)),
155               seventh_pair = convert(varchar(100), col_name(depid, depkey7)), convert(varchar(100), col_name(id, key7)),
156               eighth_pair = convert(varchar(100), col_name(depid, depkey8)), convert(varchar(100), col_name(id, key8))
157           from syskeys
158           where type = 2
159               and id = object_id(@righttab)
160               and depid = object_id(@lefttab)
161   
162           select @foundit = 1
163       end
164   
165       /*
166       **  Look for common key joins.
167       */
168       if exists (select *
169               from syskeys
170               where type = 3
171                   and id = object_id(@lefttab)
172                   and depid = object_id(@righttab))
173       begin
174           insert into #hjtab
175           select distinct first_pair = convert(varchar(100), col_name(id, key1)), convert(varchar(100), col_name(depid, depkey1)),
176               second_pair = convert(varchar(100), col_name(id, key2)), convert(varchar(100), col_name(depid, depkey2)),
177               third_pair = convert(varchar(100), col_name(id, key3)), convert(varchar(100), col_name(depid, depkey3)),
178               fourth_pair = convert(varchar(100), col_name(id, key4)), convert(varchar(100), col_name(depid, depkey4)),
179               fifth_pair = convert(varchar(100), col_name(id, key5)), convert(varchar(100), col_name(depid, depkey5)),
180               sixth_pair = convert(varchar(100), col_name(id, key6)), convert(varchar(100), col_name(depid, depkey6)),
181               seventh_pair = convert(varchar(100), col_name(id, key7)), convert(varchar(100), col_name(depid, depkey7)),
182               eighth_pair = convert(varchar(100), col_name(id, key8)), convert(varchar(100), col_name(depid, depkey8))
183           from syskeys
184           where type = 3
185               and id = object_id(@lefttab)
186               and depid = object_id(@righttab)
187   
188           select @foundit = 1
189       end
190   
191       if exists (select *
192               from syskeys
193               where type = 3
194                   and id = object_id(@righttab)
195                   and depid = object_id(@lefttab))
196       begin
197           insert into #hjtab
198           select distinct first_pair = convert(varchar(100), col_name(depid, depkey1)), convert(varchar(100), col_name(id, key1)),
199               second_pair = convert(varchar(100), col_name(depid, depkey2)), convert(varchar(100), col_name(id, key2)),
200               third_pair = convert(varchar(100), col_name(depid, depkey3)), convert(varchar(100), col_name(id, key3)),
201               fourth_pair = convert(varchar(100), col_name(depid, depkey4)), convert(varchar(100), col_name(id, key4)),
202               fifth_pair = convert(varchar(100), col_name(depid, depkey5)), convert(varchar(100), col_name(id, key5)),
203               sixth_pair = convert(varchar(100), col_name(depid, depkey6)), convert(varchar(100), col_name(id, key6)),
204               seventh_pair = convert(varchar(100), col_name(depid, depkey7)), convert(varchar(100), col_name(id, key7)),
205               eighth_pair = convert(varchar(100), col_name(depid, depkey8)), convert(varchar(100), col_name(id, key8))
206           from syskeys
207           where type = 3
208               and id = object_id(@righttab)
209               and depid = object_id(@lefttab)
210   
211           select @foundit = 1
212       end
213   
214       /*
215       **  We got a foreignkey and we didn't get a common key.
216       */
217       if @foundit = 1
218       begin
219           /* Adaptive Server has expanded all '*' elements in the following statement */ select distinct #hjtab.a1, #hjtab.a2, #hjtab.b1, #hjtab.b2, #hjtab.c1, #hjtab.c2, #hjtab.d1, #hjtab.d2, #hjtab.e1, #hjtab.e2, #hjtab.f1, #hjtab.f2, #hjtab.g1, #hjtab.g2, #hjtab.h1, #hjtab.h2
220           into #hjtab_dis
221           from #hjtab
222   
223           exec sp_autoformat @fulltabname = #hjtab_dis
224   
225           drop table #hjtab
226           drop table #hjtab_dis
227   
228           return (0)
229       end
230   
231       /*
232       **  We didn't find any defined joins so we'll look for common user types.
233       */
234       if exists (select *
235               from syscolumns a, syscolumns b
236               where a.id = object_id(@lefttab)
237                   and b.id = object_id(@righttab)
238                   and a.usertype = b.usertype
239                   and a.usertype > 99)
240       begin
241           select distinct first_pair = col_name(a.id, a.colid),
242               first_pair_second = col_name(b.id, b.colid)
243           into #hjtab1
244           from syscolumns a, syscolumns b
245           where a.id = object_id(@lefttab)
246               and b.id = object_id(@righttab)
247               and a.usertype = b.usertype
248               and a.usertype > 99
249   
250           exec sp_autoformat @fulltabname = #hjtab1,
251               @orderby = "order by first_pair"
252   
253           drop table #hjtab1
254   
255           /*
256           **  If we found something, we can stop.
257           */
258           if @@rowcount > 0
259               return (0)
260       end
261   
262       /*
263       **  We couldn't find anything so far so we'll see if we have anything
264       **  that just shares the same physical type.
265       */
266       if exists (select *
267               from syscolumns a, syscolumns b, master.dbo.spt_values y,
268                   master.dbo.spt_values z
269               where a.id = object_id(@lefttab)
270                   and b.id = object_id(@righttab)
271                   and a.id != b.id
272                   and a.name = b.name
273                   and y.type = "J"
274                   and a.type = y.low
275                   and z.type = "J"
276                   and b.type = z.low
277                   and y.number = z.number)
278       begin
279           select distinct first_pair = col_name(a.id, a.colid),
280               first_pair_second = col_name(b.id, b.colid)
281           into #hjtab2
282           from syscolumns a, syscolumns b, master.dbo.spt_values y,
283               master.dbo.spt_values z
284           where a.id = object_id(@lefttab)
285               and b.id = object_id(@righttab)
286               and a.id != b.id
287               and a.name = b.name
288               and y.type = "J"
289               and a.type = y.low
290               and z.type = "J"
291               and b.type = z.low
292               and y.number = z.number
293           exec sp_autoformat @fulltabname = #hjtab2,
294               @orderby = "order by first_pair"
295   
296           drop table #hjtab2
297       end
298   
299       return (0)
300   


exec sp_procxmode 'sp_helpjoins', 'AnyMode'
go

Grant Execute on sp_helpjoins to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 238
 QJWI 5 Join or Sarg Without Index 247
 QJWI 5 Join or Sarg Without Index 271
 QJWI 5 Join or Sarg Without Index 274
 QJWI 5 Join or Sarg Without Index 276
 QJWI 5 Join or Sarg Without Index 286
 QJWI 5 Join or Sarg Without Index 289
 QJWI 5 Join or Sarg Without Index 291
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 223
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 250
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 293
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 121
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 135
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 144
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 158
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 170
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 184
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 193
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 207
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 239
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 248
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 274
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 276
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 289
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 291
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 TNOU 4 Table with no unique index sybsystemprocs..syskeys sybsystemprocs..syskeys
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public sybsystemprocs..sp_helpjoins  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..syskeys  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNEJ 3 'Not Equal' join 271
 MNEJ 3 'Not Equal' join 286
 MNER 3 No Error Check should check @@error after insert 125
 MNER 3 No Error Check should check @@error after insert 148
 MNER 3 No Error Check should check @@error after insert 174
 MNER 3 No Error Check should check @@error after insert 197
 MNER 3 No Error Check should check @@error after select into 219
 MNER 3 No Error Check should check return value of exec 223
 MNER 3 No Error Check should check @@error after select into 241
 MNER 3 No Error Check should check return value of exec 250
 MNER 3 No Error Check should check @@error after select into 279
 MNER 3 No Error Check should check return value of exec 293
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 259
 MUCO 3 Useless Code Useless Brackets 299
 MUIN 3 Column created using implicit nullability 95
 QCTC 3 Conditional Table Creation 219
 QCTC 3 Conditional Table Creation 241
 QCTC 3 Conditional Table Creation 279
 QDIS 3 Check correct use of 'select distinct' 241
 QDIS 3 Check correct use of 'select distinct' 279
 QGWO 3 Group by/Distinct/Union without order by 126
 QGWO 3 Group by/Distinct/Union without order by 149
 QGWO 3 Group by/Distinct/Union without order by 175
 QGWO 3 Group by/Distinct/Union without order by 198
 QGWO 3 Group by/Distinct/Union without order by 219
 QGWO 3 Group by/Distinct/Union without order by 241
 QGWO 3 Group by/Distinct/Union without order by 279
 QISO 3 Set isolation level 35
 QNAJ 3 Not using ANSI Inner Join 235
 QNAJ 3 Not using ANSI Inner Join 244
 QNAJ 3 Not using ANSI Inner Join 267
 QNAJ 3 Not using ANSI Inner Join 282
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
236
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
237
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
245
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
246
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
269
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
270
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
284
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
285
 MSUB 2 Subquery Marker 70
 MSUB 2 Subquery Marker 82
 MSUB 2 Subquery Marker 119
 MSUB 2 Subquery Marker 142
 MSUB 2 Subquery Marker 168
 MSUB 2 Subquery Marker 191
 MSUB 2 Subquery Marker 234
 MSUB 2 Subquery Marker 266
 MTR1 2 Metrics: Comments Ratio Comments: 20% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 46 = 51dec - 7exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 152 22
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns} 0 234
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns} 0 241
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 266
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscolumns, sv=master..spt_values, sv2=master..spt_values} 0 279

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
writes table tempdb..#hjtab1 (1) 
reads table master..spt_values (1)  
writes table tempdb..#hjtab_dis (1) 
writes table tempdb..#hjtab2 (1) 
reads table sybsystemprocs..syscolumns  
calls proc sybsystemprocs..sp_autoformat  
   reads table master..systypes (1)  
   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)  
   calls proc sybsystemprocs..sp_namecrack  
reads table sybsystemprocs..syskeys  
read_writes table tempdb..#hjtab (1)