DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_getindexinfo  14 déc. 14Defects Propagation Dependencies

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


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 15
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_getindexinfo  
 MUCO 3 Useless Code Useless Brackets in create proc 15
 MUCO 3 Useless Code Useless Brackets 28
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 211
 QISO 3 Set isolation level 38
 VUNU 3 Variable is not used @indid 22
 VUNU 3 Variable is not used @lastindid 23
 MDYS 2 Dynamic SQL Marker 206
 MTR1 2 Metrics: Comments Ratio Comments: 6% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 9dec - 1exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 38 15

DEPENDENCIES