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 | |
![]() | 60 |
![]() | 94 |
![]() | 157 |
![]() | 93 |
![]() | 154 |
![]() | master..spt_values |
![]() | sybsystemprocs..syskeys |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 58 |
![]() | 63 |
![]() | 71 |
![]() | 94 |
![]() | 126 |
![]() | 132 |
![]() | 157 |
![]() | 47 |
![]() | 97 |
![]() | 114 |
![]() | 128 |
![]() | 160 |
![]() | 71 |
![]() | 132 |
![]() | 37 |
![]() | 92 |
![]() | 153 |
![]() | 73 |
![]() | 74 |
![]() | 75 |
![]() | 76 |
![]() | 77 |
![]() | 78 |
![]() | 79 |
![]() | 80 |
![]() | 82 |
![]() | 83 |
![]() | 85 |
![]() | 86 |
![]() | 87 |
![]() | 88 |
![]() | 89 |
![]() | 90 |
![]() | 135 |
![]() | 136 |
![]() | 137 |
![]() | 138 |
![]() | 139 |
![]() | 140 |
![]() | 141 |
![]() | 142 |
![]() | 144 |
![]() | 145 |
![]() | 146 |
![]() | 147 |
![]() | 148 |
![]() | 149 |
![]() | 150 |
![]() | 151 |
![]() | 122 |
![]() | 108 |
![]() | 120 |
![]() | 23 |
![]() | 23 |
![]() | 23 |
![]() | 71 |
![]() | 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 ![]() |