Database | Proc | Application | Created | Links |
sybsystemprocs | sp_ijdbc_getbestrowidentifier ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_ijdbc_getbestrowidentifier 4 */ 5 6 7 /* Get a description of a table's optimal set of columns that uniquely 8 ** identifies a row 9 ** Usually it's the unique primary key index column or the identity field 10 */ 11 12 create procedure sp_ijdbc_getbestrowidentifier( 13 @table_qualifier varchar(32) = null, 14 @table_owner varchar(32) = null, 15 @table_name varchar(257), 16 @scope int, 17 @nullable smallint) 18 as 19 declare @indid int 20 declare @table_id int 21 declare @dbname char(30) 22 declare @owner char(30) 23 declare @msg char(70) 24 25 if @@trancount = 0 26 begin 27 set chained off 28 end 29 30 set transaction isolation level 1 31 32 33 /* get database name */ 34 select @dbname = db_name() 35 36 /* we don't want a temp table unless we're in our temporary db */ 37 if @table_name like '#%' and @dbname != db_name(tempdb_id()) 38 begin 39 exec sp_getmessage 17676, @msg output 40 raiserror 17676 @msg 41 return (1) 42 end 43 44 if @table_qualifier is not null 45 begin 46 /* if qualifier doesn't match current database */ 47 if @dbname != @table_qualifier 48 begin 49 exec sp_getmessage 18039, @msg output 50 raiserror 18039 @msg 51 return (1) 52 end 53 end 54 55 if (@table_owner is null) 56 begin 57 select @table_owner = '%' 58 end 59 else 60 begin 61 62 if (charindex('%', @table_owner) > 0) 63 begin 64 exec sp_getmessage 17993, @msg output 65 raiserror 17993 @msg, @table_owner 66 return (1) 67 end 68 69 /* 70 ** if there is a '_' character in @table_owner, 71 ** then we need to make it work literally in the like 72 ** clause. 73 */ 74 if (charindex('_', @table_owner) > 0) 75 begin 76 exec sp_ijdbc_escapeliteralforlike 77 @table_owner output 78 end 79 end 80 81 82 if (@table_name is null) 83 begin 84 exec sp_getmessage 17993, @msg output 85 raiserror 17993 @msg, 'NULL' 86 return (1) 87 end 88 89 delete #tmp_getbestrowidentifier 90 91 if ((select count(*) 92 from sysobjects 93 where user_name(uid) like @table_owner ESCAPE '\' 94 and name = @table_name) = 0) 95 begin 96 exec sp_getmessage 17674, @msg output 97 raiserror 17674 @msg, @table_name 98 return 99 end 100 101 select owner = user_name(o.uid), 102 full_table_name = user_name(o.uid) + '.' + @table_name, 103 table_id = object_id(user_name(o.uid) + '.' + @table_name) 104 into #temp_owner 105 from sysobjects o 106 where o.name like @table_name ESCAPE '\' 107 and user_name(o.uid) like @table_owner ESCAPE '\' 108 109 select min_indid = min(s.indid), 110 table_id = tow.table_id 111 into #temp_ind 112 from sysindexes s, #temp_owner tow 113 where s.indid > 0 114 and s.id = tow.table_id 115 group by tow.table_id 116 117 118 /* Sybase's only PSEUDO_COLUMN is called SYB_IDENTITY_COL and */ 119 /* is only generated when dboption 'auto identity' is set on */ 120 121 122 insert #tmp_getbestrowidentifier 123 select convert(smallint, 0), 'SYB_IDENTITY_COL', 2, 'NUMERIC', 10, 124 'not used', 0, 2 125 from #temp_owner 126 where exists (select name from syscolumns 127 where id = #temp_owner.table_id 128 and name = 'SYB_IDENTITY_COL') 129 130 insert #tmp_getbestrowidentifier 131 select 132 convert(smallint, 0), 133 index_col(tpo.full_table_name, x.indid, c.colid), 134 d.data_type + convert(smallint, isnull(d.aux, 135 ascii(substring('666AAA@@@CB??GG', 136 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1)) 137 - 60)), 138 rtrim(substring(d.type_name, 1 + isnull(d.aux, 139 ascii(substring('III<<<MMMI<<A<A', 140 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1)) 141 - 60), 13)), 142 isnull(d.data_precision, convert(int, c2.length)) 143 + isnull(d.aux, convert(int, 144 ascii(substring('???AAAFFFCKFOLS', 145 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1)) 146 - 60)), 147 'not used', 148 /*isnull(d.length, convert(int,c2.length)) 149 + convert(int, isnull(d.aux, 150 ascii(substring('AAA151 2*(d.ss_dtype%35+1)+2-8/c2.length, 1)) 152 -64)),*/ 153 d.numeric_scale + convert(smallint, 154 isnull(d.aux, 155 ascii(substring('<<<<<<<<<<<<<<?', 156 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1)) 157 - 60)), 158 1 159 from 160 sysindexes x, 161 syscolumns c, 162 sybsystemprocs.dbo.spt_jdatatype_info d, 163 systypes t, 164 syscolumns c2, /* self-join to generate list of index 165 ** columns and to extract datatype names */ 166 #temp_owner tpo, 167 #temp_ind tind 168 where 169 x.id = tpo.table_id 170 and c2.name = index_col(tpo.full_table_name, tind.min_indid, c.colid) 171 and c2.id = x.id 172 and tind.table_id = tpo.table_id 173 and c.id = x.id 174 and c.colid < keycnt + (x.status & 16) / 16 175 and x.indid = tind.min_indid 176 and c2.type = d.ss_dtype 177 and c2.usertype *= t.usertype 178 and not exists (select name from syscolumns 179 where id = tpo.table_id 180 and name = 'SYB_IDENTITY_COL') 181 182 return (0) 183
exec sp_procxmode 'sp_ijdbc_getbestrowidentifier', 'AnyMode' go Grant Execute on sp_ijdbc_getbestrowidentifier to public go
DEPENDENCIES |
PROCS AND TABLES USED calls proc sybsystemprocs..sp_getmessage ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..syscolumns ![]() calls proc sybsystemprocs..sp_ijdbc_escapeliteralforlike ![]() reads table sybsystemprocs..systypes ![]() reads table sybsystemprocs..sysindexes ![]() writes table tempdb..#tmp_getbestrowidentifier (1) read_writes table tempdb..#temp_ind (1) read_writes table tempdb..#temp_owner (1) reads table sybsystemprocs..spt_jdatatype_info ![]() |