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
