1
2
3 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
4
5 /*
6 ** Messages for "sp_oledb_getindexinfo" 18039
7 **
8 ** 18039, "Table qualifier must be name of current database."
9 ** 18040, "Catalog procedure '%1!' can not be run in a transaction.
10 **
11 */
12
13 create procedure sp_oledb_getindexinfo(
14 @table_name varchar(771) = null,
15 @table_owner varchar(32) = null,
16 @table_qualifier varchar(32) = null,
17 @index_name varchar(771) = '%',
18 @type smallint = null)
19 as
20 declare @indid int
21 declare @lastindid int
22 declare @full_table_name varchar(1543)
23 declare @startedInTransaction bit
24 declare @cmd varchar(5000)
25
26 if (@@trancount > 0)
27 select @startedInTransaction = 1
28 else
29 select @startedInTransaction = 0
30
31 if @@trancount = 0
32 begin
33 set chained off
34 end
35
36 set transaction isolation level 1
37 if (@startedInTransaction = 1)
38 save transaction oledb_keep_temptable_tx
39
40 if @table_name is null
41 begin
42 select @cmd = "select
43 TABLE_CATALOG=db_name(), -- table_catalog
44 TABLE_SCHEMA=user_name(o.uid), -- table_schema
45 TABLE_NAME=o.name, -- table_name
46 INDEX_CATALOG=db_name(), -- index_catalog
47 INDEX_SCHEMA=o.name, -- index_schema
48 INDEX_NAME=x.name, -- index_name
49 PRIMARY_KEY= case
50 when x.status&2048 = 2048 then convert(bit,1)
51 else
52 convert(bit,0)
53 end, -- primary_key
54 'UNIQUE' = case
55 when x.status&2 != 2 then convert(bit,0)
56 else
57 convert(bit,1)
58 end, -- uniqueval
59 'CLUSTERED' = case
60 when x.status2&512 = 512 then convert(bit,1)
61 else
62 convert(bit,1)
63 end, -- clusteredval
64 TYPE= case
65 when x.indid > 1 then convert(smallint,4)
66 when x.status2&512 = 512 then convert(smallint,1)
67 else
68 convert(smallint,1)
69 end, -- type
70 FILL_FACTOR=x.fill_factor, -- fill_factor
71 INITIAL_SIZE=null, -- initial_size
72 NULLS=4, -- nulls
73 SORT_BOOKMARKS=convert(bit,0), -- sort_bookmarks
74 AUTO_UPDATE=convert(bit,1), -- auto_update
75 NULL_COLLATION=4, -- null_collation
76 ORDINAL_POSITION=convert(int,colid), -- ordinal_position
77 COLUMN_NAME=INDEX_COL(o.name,indid,colid), -- column name
78 COLUMN_GUID = convert(varchar(36), null),
79 COLUMN_PROPID=null, -- column propid
80 COLLATION=1, -- collation
81 CARDINALITY=case
82 when x.indid > 1 then NULL
83 else
84 -- rowcnt(x.doampg)
85 row_count(db_id(), x.id)
86 end, -- cardinality
87 PAGES=case
88 when x.indid > 1 then NULL
89 else
90 -- data_pgs(x.id,doampg)
91 data_pages(db_id(), x.id,
92 case
93 when x.indid = 1
94 then 0
95 else x.indid
96 end)
97 end, -- pages
98 FILTER_CONDITION=null, -- filter condition
99 INTEGRATED=convert(bit,1) -- integrated
100 from sysindexes x, syscolumns c, sysobjects o
101 where x.id = o.id
102 and x.id = c.id
103 and c.colid < keycnt+(x.status&16)/16
104 and o.type = 'U' and x.id = object_id(o.name)
105 and x.indid < 255
106 and o.name like '" + @table_name + "'
107 and (x.name like '" + @index_name + "' or x.name is null)"
108 end
109 else
110 begin
111 /*
112 ** Fully qualify table name.
113 */
114 if @table_owner is null
115 begin /* If unqualified table name */
116 select @full_table_name = @table_name
117 select @table_owner = '%'
118 end
119 else
120 begin /* Qualified table name */
121 select @full_table_name = @table_owner + '.' + @table_name
122 end
123
124 if @table_qualifier is null
125 select @table_qualifier = '%'
126
127 select @cmd = "select
128 TABLE_CATALOG=db_name(), -- table_catalog
129 TABLE_SCHEMA=user_name(o.uid), -- table_schema
130 TABLE_NAME=o.name, -- table_name
131 INDEX_CATALOG=db_name(), -- index_catalog
132 INDEX_SCHEMA=o.name, -- index_schema
133 INDEX_NAME=x.name, -- index_name
134 PRIMARY_KEY= case
135 when x.status&2048 = 2048 then convert(bit,1)
136 else
137 convert(bit,0)
138 end, -- primary_key
139 'UNIQUE' = case
140 when x.status&2 != 2 then convert(bit,0)
141 else
142 convert(bit,1)
143 end, -- uniqueval
144 'CLUSTERED' = case
145 when x.status2&512 = 512 then convert(bit,1)
146 else
147 convert(bit,1)
148 end, -- clusteredval
149 TYPE= case
150 when x.indid > 1 then 4
151 when x.status2&512 = 512 then 1
152 else
153 1
154 end, -- type
155 FILL_FACTOR=x.fill_factor, -- fill_factor
156 INITIAL_SIZE=null, -- initial_size
157 NULLS=4, -- nulls
158 SORT_BOOKMARKS=convert(bit,0), -- sort_bookmarks
159 AUTO_UPDATE=convert(bit,1), -- auto_update
160 NULL_COLLATION=4, -- null_collation
161 ORDINAL_POSITION=convert(int,colid), -- ordinal_position
162 COLUMN_NAME=INDEX_COL('" + @full_table_name + "',indid,colid), -- column name
163 COLUMN_GUID = convert(varchar(36), null),
164 COLUMN_PROPID=null, -- column propid
165 COLLATION=case
166 when index_colorder('" + @full_table_name + "',indid,colid) = 'ASC' then convert(smallint, 1)
167 when index_colorder('" + @full_table_name + "',indid,colid) = 'DESC' then convert(smallint, 2)
168 else convert(smallint, 0)
169 end, -- collation
170 CARDINALITY=case
171 when x.indid > 1 then NULL
172 else
173 -- rowcnt(x.doampg)
174 row_count(db_id(), x.id)
175 end, -- cardinality
176 PAGES=case
177 when x.indid > 1 then NULL
178 else
179 -- data_pgs(x.id,doampg)
180 data_pages(db_id(), x.id,
181 case
182 when x.indid = 1
183 then 0
184 else x.indid
185 end)
186 end, -- pages
187 FILTER_CONDITION=null, -- filter condition
188 INTEGRATED=convert(bit,1) -- integrated
189 from sysindexes x, syscolumns c, sysobjects o
190 where x.id = object_id('" + @full_table_name + "')
191 and x.id = o.id
192 and x.id = c.id
193 and c.colid < keycnt+(x.status&16)/16
194 and (x.indid > 0 and x.indid < 255)
195 and db_name() like '" + @table_qualifier + "'
196 and user_name(o.uid) like '" + @table_owner + "'
197 and (x.name like '" + @index_name + "' or x.name is null)"
198
199 end /*for the @table_name is not null*/
200
201 if @type is not null
202 select @cmd = @cmd + " and convert(smallint," + convert(varchar(10), @type) + ") in (1,4)"
203 select @cmd = @cmd + " order by TABLE_CATALOG, TABLE_SCHEMA, INDEX_NAME, TYPE, TABLE_NAME"
204 execute (@cmd)
205
206 if (@startedInTransaction = 1)
207 rollback transaction oledb_keep_temptable_tx
208
209 return (0)
210
exec sp_procxmode 'sp_oledb_getindexinfo', 'AnyMode'
go
Grant Execute on sp_oledb_getindexinfo to public
go