DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_getindexinfo  31 Aug 14Defects Dependencies

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
DEFECTS
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 13
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_getindexinfo  
 MUCO 3 Useless Code Useless Brackets in create proc 13
 MUCO 3 Useless Code Useless Brackets 26
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 209
 QISO 3 Set isolation level 36
 VUNU 3 Variable is not used @indid 20
 VUNU 3 Variable is not used @lastindid 21
 MDYS 2 Dynamic SQL Marker 204
 MTR1 2 Metrics: Comments Ratio Comments: 5% 13
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 8dec - 1exi + 2 13
 MTR3 2 Metrics: Query Complexity Complexity: 38 13

DEPENDENCIES