Database	Proc	Application	Created	Links
sybsystemprocs	sp_helpjoins		31 Aug 14	Defects 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