DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpkey  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/help */
4     /*
5     ** Messages for "sp_helpkey"            17660
6     **
7     ** 17390, "Table or view name must be in 'current' database."
8     ** 17492, "The table or view named doesn't exist in the current database."
9     ** 17660, "No defined keys for this object."
10    ** 17661, " -- none --"
11    ** 17662, "NOTE : For information on declarative integrity features : PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK CONSTRAINT, REFERENTIAL CONSTRAINT, NULL/NOT NULL, and DEFAULT, use sp_helpconstr, a new system stored procedure."
12    */
13    
14    /*
15    ** IMPORTANT NOTE:
16    ** This stored procedure uses the built-in function object_id() in the
17    ** where clause of a select query. If you intend to change this query
18    ** or use the object_id() or db_id() builtin in this procedure, please read the
19    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
20    ** pertaining to object-id's and db-id's outlined there, are followed.
21    */
22    
23    create procedure sp_helpkey
24        @tabname varchar(767) = null /* table or view we want to check */
25    as
26    
27        declare @note varchar(1024)
28        declare @msg varchar(1024)
29        declare @none varchar(30)
30    
31    
32        if @@trancount = 0
33        begin
34            set chained off
35        end
36    
37        set transaction isolation level 1
38    
39        /*
40        **  Check to see that the object names are local to the current database.
41        */
42        if @tabname like "%.%.%" and
43            substring(@tabname, 1, charindex(".", @tabname) - 1) != db_name()
44        begin
45            /* 17390, "Table or view name must be in 'current' database." */
46            raiserror 17390
47            return (1)
48        end
49    
50        /*
51        ** This procedure should eventually become obsolete since with System 10
52        ** we support declarative constraints.  This constraint information is
53        ** available through sp_helpconstr.  The server will not record any information
54        ** in syskeys.  Do this only if this procedure is executed directly.
55        */
56        if @@nestlevel = 1
57        begin
58            exec sp_getmessage 17662, @note out
59            print @note
60            print ""
61        end
62    
63        exec sp_getmessage 17661, @none out
64    
65        /*
66        **  If @tabname is null, show all the keys.
67        */
68        if @tabname is null
69        begin
70    
71            select keytype = v.name, object = object_name(k.id),
72                related_object = isnull(object_name(k.depid), @none),
73                object_keys = convert(varchar(80), col_name(k.id, key1)) + ", "
74                + convert(varchar(80), isnull(col_name(k.id, key2), "*")) + ", "
75                + convert(varchar(80), isnull(col_name(k.id, key3), "*")) + ", "
76                + convert(varchar(80), isnull(col_name(k.id, key4), "*")) + ", "
77                + convert(varchar(80), isnull(col_name(k.id, key5), "*")) + ", "
78                + convert(varchar(80), isnull(col_name(k.id, key6), "*")) + ", "
79                + convert(varchar(80), isnull(col_name(k.id, key7), "*")) + ", "
80                + convert(varchar(80), isnull(col_name(k.id, key8), "*")),
81                related_keys =
82                convert(varchar(80), isnull(col_name(k.depid, depkey1), "*")) + ", "
83                + convert(varchar(80), isnull(col_name(k.depid, depkey2), "*")) + ", "
84    
85                + convert(varchar(80), isnull(col_name(k.depid, depkey3), "*")) + ", "
86                + convert(varchar(80), isnull(col_name(k.depid, depkey4), "*")) + ", "
87                + convert(varchar(80), isnull(col_name(k.depid, depkey5), "*")) + ", "
88                + convert(varchar(80), isnull(col_name(k.depid, depkey6), "*")) + ", "
89                + convert(varchar(80), isnull(col_name(k.depid, depkey7), "*")) + ", "
90                + convert(varchar(80), isnull(col_name(k.depid, depkey8), "*"))
91            into #sphelpkeys1rs
92            from syskeys k, master.dbo.spt_values v
93            where k.type = v.number and v.type = 'K'
94            exec sp_autoformat @fulltabname = #sphelpkeys1rs,
95                @orderby = "order by object, keytype, related_object"
96            drop table #sphelpkeys1rs
97            return (0)
98        end
99    
100       /*
101       **  We're looking for a particular table or view.
102       */
103       else
104       begin
105           /*
106           **  Make sure the table or view exists.
107           */
108           if not exists (select id
109                   from sysobjects
110                   where id = object_id(@tabname))
111           begin
112               /* 17492, "The table or view named doesn't exist in the current database." */
113               raiserror 17492
114               return (1)
115           end
116   
117           /*
118           **  See if any keys exist.
119           */
120           if not exists (select *
121                   from syskeys
122                   where id = object_id(@tabname) or
123                       depid = object_id(@tabname))
124           begin
125               /* 17660, "No defined keys for this object." */
126               exec sp_getmessage 17660, @msg output
127               print @msg
128               return (1)
129           end
130   
131   
132           select keytype = v.name,
133               object = object_name(k.id),
134               related_object = isnull(object_name(k.depid), @none),
135               object_keys = convert(varchar(80), col_name(k.id, key1)) + ", "
136               + convert(varchar(80), isnull(col_name(k.id, key2), "*")) + ", "
137               + convert(varchar(80), isnull(col_name(k.id, key3), "*")) + ", "
138               + convert(varchar(80), isnull(col_name(k.id, key4), "*")) + ", "
139               + convert(varchar(80), isnull(col_name(k.id, key5), "*")) + ", "
140               + convert(varchar(80), isnull(col_name(k.id, key6), "*")) + ", "
141               + convert(varchar(80), isnull(col_name(k.id, key7), "*")) + ", "
142               + convert(varchar(80), isnull(col_name(k.id, key8), "*")),
143               related_keys =
144               convert(varchar(80), isnull(col_name(k.depid, depkey1), "*")) + ", "
145               + convert(varchar(80), isnull(col_name(k.depid, depkey2), "*")) + ", "
146               + convert(varchar(80), isnull(col_name(k.depid, depkey3), "*")) + ", "
147               + convert(varchar(80), isnull(col_name(k.depid, depkey4), "*")) + ", "
148               + convert(varchar(80), isnull(col_name(k.depid, depkey5), "*")) + ", "
149               + convert(varchar(80), isnull(col_name(k.depid, depkey6), "*")) + ", "
150               + convert(varchar(80), isnull(col_name(k.depid, depkey7), "*")) + ", "
151               + convert(varchar(80), isnull(col_name(k.depid, depkey8), "*"))
152           into #sphelpkeys2rs
153           from syskeys k, master.dbo.spt_values v
154           where k.type = v.number and v.type = 'K'
155               and (k.id = object_id(@tabname) or
156                   k.depid = object_id(@tabname))
157           exec sp_autoformat @fulltabname = #sphelpkeys2rs,
158               @orderby = "order by object, keytype, related_object"
159           drop table #sphelpkeys2rs
160           return (0)
161       end
162   


exec sp_procxmode 'sp_helpkey', 'AnyMode'
go

Grant Execute on sp_helpkey to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 60
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 94
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 157
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 93
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 154
 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_helpkey  
 MGTP 3 Grant to public sybsystemprocs..syskeys  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 58
 MNER 3 No Error Check should check return value of exec 63
 MNER 3 No Error Check should check @@error after select into 71
 MNER 3 No Error Check should check return value of exec 94
 MNER 3 No Error Check should check return value of exec 126
 MNER 3 No Error Check should check @@error after select into 132
 MNER 3 No Error Check should check return value of exec 157
 MUCO 3 Useless Code Useless Brackets 47
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 160
 QCTC 3 Conditional Table Creation 71
 QCTC 3 Conditional Table Creation 132
 QISO 3 Set isolation level 37
 QNAJ 3 Not using ANSI Inner Join 92
 QNAJ 3 Not using ANSI Inner Join 153
 QNUA 3 Should use Alias: Column key1 should use alias k 73
 QNUA 3 Should use Alias: Column key2 should use alias k 74
 QNUA 3 Should use Alias: Column key3 should use alias k 75
 QNUA 3 Should use Alias: Column key4 should use alias k 76
 QNUA 3 Should use Alias: Column key5 should use alias k 77
 QNUA 3 Should use Alias: Column key6 should use alias k 78
 QNUA 3 Should use Alias: Column key7 should use alias k 79
 QNUA 3 Should use Alias: Column key8 should use alias k 80
 QNUA 3 Should use Alias: Column depkey1 should use alias k 82
 QNUA 3 Should use Alias: Column depkey2 should use alias k 83
 QNUA 3 Should use Alias: Column depkey3 should use alias k 85
 QNUA 3 Should use Alias: Column depkey4 should use alias k 86
 QNUA 3 Should use Alias: Column depkey5 should use alias k 87
 QNUA 3 Should use Alias: Column depkey6 should use alias k 88
 QNUA 3 Should use Alias: Column depkey7 should use alias k 89
 QNUA 3 Should use Alias: Column depkey8 should use alias k 90
 QNUA 3 Should use Alias: Column key1 should use alias k 135
 QNUA 3 Should use Alias: Column key2 should use alias k 136
 QNUA 3 Should use Alias: Column key3 should use alias k 137
 QNUA 3 Should use Alias: Column key4 should use alias k 138
 QNUA 3 Should use Alias: Column key5 should use alias k 139
 QNUA 3 Should use Alias: Column key6 should use alias k 140
 QNUA 3 Should use Alias: Column key7 should use alias k 141
 QNUA 3 Should use Alias: Column key8 should use alias k 142
 QNUA 3 Should use Alias: Column depkey1 should use alias k 144
 QNUA 3 Should use Alias: Column depkey2 should use alias k 145
 QNUA 3 Should use Alias: Column depkey3 should use alias k 146
 QNUA 3 Should use Alias: Column depkey4 should use alias k 147
 QNUA 3 Should use Alias: Column depkey5 should use alias k 148
 QNUA 3 Should use Alias: Column depkey6 should use alias k 149
 QNUA 3 Should use Alias: Column depkey7 should use alias k 150
 QNUA 3 Should use Alias: Column depkey8 should use alias k 151
 QTLO 3 Top-Level OR 122
 MSUB 2 Subquery Marker 108
 MSUB 2 Subquery Marker 120
 MTR1 2 Metrics: Comments Ratio Comments: 29% 23
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 12dec - 6exi + 2 23
 MTR3 2 Metrics: Query Complexity Complexity: 55 23
 PRED_QUERY_COLLECTION 2 {k=sybsystemprocs..syskeys, sv=master..spt_values} 0 71
 PRED_QUERY_COLLECTION 2 {k=sybsystemprocs..syskeys, sv=master..spt_values} 0 132

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#sphelpkeys2rs (1) 
calls proc sybsystemprocs..sp_autoformat  
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_namecrack  
   reads table master..systypes (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
reads table sybsystemprocs..sysobjects  
writes table tempdb..#sphelpkeys1rs (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
reads table sybsystemprocs..syskeys  
reads table master..spt_values (1)  

CALLERS
called by proc sybsystemprocs..sp_help