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