DatabaseProcApplicationCreatedLinks
sybsystemprocssp_statistics  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_statistics"          18039
6     **
7     ** 18039, "Table qualifier must be name of current database."
8     ** 18040, "Catalog procedure '%1!' can not be run in a transaction.
9     **
10    */
11    
12    /*
13    ** Sp_statistics returns statistics for the given table, passed as first 
14    ** argument. A row is returned for the table and then for each index found
15    ** in sysindexes, starting with lowest value index id in sysindexes and 
16    ** proceeding through the highest value index.  
17    **
18    ** Returned rows consist of the columns:
19    ** table qualifier (database name), table owner, table name from sysobjects, 
20    ** non_unique (0/1), index qualifier (same as table name), 
21    ** index name from sysindexes, type (SQL_INDEX_CLUSTERED/SQL_INDEX_OTHER), 
22    ** sequence in index, column name, collation, table cardinality (row count), 
23    ** and number of pages used by table (doampg).
24    ** table cardinality (row count), and number of pages used by 
25    ** table (datoampage).
26    */
27    
28    create or replace procedure sp_statistics(
29        @table_name varchar(257),
30        @table_owner varchar(257) = null,
31        @table_qualifier varchar(257) = null,
32        @index_name varchar(257) = '%',
33        @is_unique char(1) = 'N')
34    as
35        declare @indid int
36        declare @lastindid int
37        declare @full_table_name char(520)
38        declare @int_limit int
39    
40        /*
41        ** Verify table qualifier is name of current database.
42        */
43        if @table_qualifier is not null
44        begin
45            if db_name() != @table_qualifier
46            begin /* If qualifier doesn't match current database */
47                /*
48                ** 18039, "Table qualifier must be name of current database."
49                */
50                raiserror 18039
51                return (1)
52            end
53        end
54    
55        if @@trancount > 0
56        begin
57            /*
58            ** 18040, "Catalog procedure '%1!' can not be run in a transaction.
59            */
60            raiserror 18040, "sp_statistics"
61            return (1)
62        end
63        else
64        begin
65            set chained off
66        end
67    
68        set transaction isolation level 1
69    
70        create table #TmpIndex(
71            table_qualifier varchar(257),
72            table_owner varchar(257),
73            table_name varchar(257),
74            index_qualifier varchar(257) null,
75            index_name varchar(257) null,
76            non_unique smallint null,
77            type smallint,
78            seq_in_index smallint null,
79            column_name varchar(257) null,
80            collation char(1) null,
81            index_id int null,
82            cardinality int null,
83            pages int null,
84            status smallint,
85            status2 smallint)
86    
87        /*
88        ** Fully qualify table name.
89        */
90        if @table_owner is null
91        begin /* If unqualified table name */
92            select @full_table_name = @table_name
93        end
94        else
95        begin /* Qualified table name */
96            select @full_table_name = @table_owner + '.' + @table_name
97        end
98    
99        select @int_limit = 2147483647
100   
101       /*
102       ** Start at lowest index id, while loop through indexes. 
103       ** Create a row in #TmpIndex for every column in sysindexes, each is
104       ** followed by an row in #TmpIndex with table statistics for the preceding
105       ** index.
106       */
107       select @indid = min(indid)
108       from sysindexes
109       where id = object_id(@full_table_name)
110           and indid > 0
111           and indid < 255
112   
113       while @indid is not NULL
114       begin
115           insert #TmpIndex /* Add all columns that are in index */
116           select
117               db_name(), /* table_qualifier */
118               user_name(o.uid), /* table_owner	   */
119               o.name, /* table_name	   */
120               o.name, /* index_qualifier */
121               x.name, /* index_name	   */
122               0, /* non_unique	   */
123               1, /* SQL_INDEX_CLUSTERED */
124               colid, /* seq_in_index	   */
125               INDEX_COL(@full_table_name, x.indid, colid),
126               /* column_name	   */
127               index_colorder(@full_table_name,
128                   x.indid, colid), /* collation	   */
129               @indid, /* index_id 	   */
130               case /* cardinality	   */
131                   when row_count(db_id(), x.id) < @int_limit
132                   then row_count(db_id(), x.id)
133                   else @int_limit
134               end,
135               case /* pages	   */
136                   when data_pages(db_id(), x.id,
137                           case
138                               when x.indid = 1
139                               then 0
140                               else x.indid
141                           end) < @int_limit
142                   then data_pages(db_id(), x.id,
143                       case
144                           when x.indid = 1
145                           then 0
146                           else x.indid
147                       end)
148                   else @int_limit
149               end,
150               x.status, /* status	   */
151               x.status2 /* status2	   */
152           from sysindexes x, syscolumns c, sysobjects o
153           where x.id = object_id(@full_table_name)
154               and x.id = o.id
155               and x.id = c.id
156               and c.colid < keycnt + (x.status & 16) / 16
157               and x.indid = @indid
158   
159           /*
160           ** Save last index and increase index id to next higher value.
161           */
162           select @lastindid = @indid
163           select @indid = NULL
164   
165           select @indid = min(indid)
166           from sysindexes
167           where id = object_id(@full_table_name)
168               and indid > @lastindid
169               and indid < 255
170       end
171   
172       update #TmpIndex
173       set non_unique = 1
174       where status & 2 != 2 /* If non-unique index */
175   
176       update #TmpIndex
177       set
178           type = 3, /* SQL_INDEX_OTHER */
179           cardinality = NULL,
180           pages = NULL
181       where index_id > 1 /* If non-clustered index */
182   
183       update #TmpIndex
184       set type = 1 /* SQL_INDEX_CLUSTERED */
185       where
186           status2 & 512 = 512 /* if placement index */
187       /* 
188       ** Now add row with table statistics 
189       */
190       insert #TmpIndex
191       select
192           db_name(), /* table_qualifier */
193           user_name(o.uid), /* table_owner	   */
194           o.name, /* table_name	   */
195           null, /* index_qualifier */
196           null, /* index_name	   */
197           null, /* non_unique	   */
198           0, /* SQL_table_STAT  */
199           null, /* seq_in_index	*/
200           null, /* column_name	   */
201           null, /* collation	   */
202           0, /* index_id 	   */
203           case /* cardinality	   */
204               when row_count(db_id(), x.id) < @int_limit
205               then row_count(db_id(), x.id)
206               else @int_limit
207           end,
208           case /* pages	   */
209               when data_pages(db_id(), x.id,
210                       case
211                           when x.indid = 1
212                           then 0
213                           else x.indid
214                       end) < @int_limit
215               then data_pages(db_id(), x.id,
216                   case
217                       when x.indid = 1
218                       then 0
219                       else x.indid
220                   end)
221               else @int_limit
222           end,
223           0, /* status	   */
224           0 /* status2	   */
225       from sysindexes x, sysobjects o
226       where o.id = object_id(@full_table_name)
227           and x.id = o.id
228           and (x.indid = 0 or x.indid = 1)
229       /*  
230       ** If there are no indexes
231       ** then table stats are in a row with indid = 0
232       */
233   
234       if @is_unique != 'Y'
235       begin
236           /* If all indexes desired */
237           select
238               table_qualifier,
239               table_owner,
240               table_name,
241               non_unique,
242               index_qualifier,
243               index_name,
244               type,
245               seq_in_index,
246               column_name,
247               collation,
248               cardinality,
249               pages
250           from #TmpIndex
251           where index_name like @index_name /* If matching name */
252               or index_name is null /* If SQL_table_STAT row */
253           order by non_unique, type, index_name, seq_in_index
254   
255       end
256       else
257       begin
258           /* else only unique indexes desired */
259           select
260               table_qualifier,
261               table_owner,
262               table_name,
263               non_unique,
264               index_qualifier,
265               index_name,
266               type,
267               seq_in_index,
268               column_name,
269               collation,
270               cardinality,
271               pages
272           from #TmpIndex
273           where (non_unique = 0 /* If unique */
274                   or non_unique is NULL) /* If SQL_table_STAT row */
275               and (index_name like @index_name /* If matching name */
276                   or index_name is NULL) /* If SQL_table_STAT row */
277           order by non_unique, type, index_name, seq_in_index
278   
279       end
280       drop table #TmpIndex
281   
282       return (0)
283   


exec sp_procxmode 'sp_statistics', 'AnyMode'
go

Grant Execute on sp_statistics to public
go
RESULT SETS
sp_statistics_rset_002
sp_statistics_rset_001

DEFECTS
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
228
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 110
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 111
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 144
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 156
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 157
 QTYP 4 Comparison type mismatch smallint = int 157
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 168
 QTYP 4 Comparison type mismatch smallint = int 168
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 169
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 211
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 217
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 228
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 273
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 109
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 131
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 132
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 136
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 142
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 153
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 154
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 154
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 155
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 155
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 167
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 204
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 205
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 209
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 215
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 226
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 227
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 227
 MGTP 3 Grant to public sybsystemprocs..sp_statistics  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MLCH 3 Char type with length>30 char(520) 37
 MNER 3 No Error Check should check @@error after insert 115
 MNER 3 No Error Check should check @@error after update 172
 MNER 3 No Error Check should check @@error after update 176
 MNER 3 No Error Check should check @@error after update 183
 MNER 3 No Error Check should check @@error after insert 190
 MUCO 3 Useless Code Useless Brackets in create proc 28
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 282
 MUIN 3 Column created using implicit nullability 70
 QCRS 3 Conditional Result Set 237
 QCRS 3 Conditional Result Set 259
 QISO 3 Set isolation level 68
 QNAJ 3 Not using ANSI Inner Join 152
 QNAJ 3 Not using ANSI Inner Join 225
 QNUA 3 Should use Alias: Column colid should use alias c 124
 QNUA 3 Should use Alias: Column colid should use alias c 125
 QNUA 3 Should use Alias: Column colid should use alias c 128
 QNUA 3 Should use Alias: Column keycnt should use alias x 156
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
Uncovered: [number, colid]
155
 QTLO 3 Top-Level OR 251
 MRST 2 Result Set Marker 237
 MRST 2 Result Set Marker 259
 MTR1 2 Metrics: Comments Ratio Comments: 37% 28
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 11dec - 3exi + 2 28
 MTR3 2 Metrics: Query Complexity Complexity: 87 28
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 116
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 191

DATA PROPAGATION detailed
ColumnWritten To
@table_namesp_statistics_rset_001.column_name °.collation sp_statistics_rset_002.column_name °.collation
@table_ownersp_statistics_rset_001.column_name °.collation sp_statistics_rset_002.column_name °.collation

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_statistics_rset_001 
reads table sybsystemprocs..syscolumns  
read_writes table tempdb..#TmpIndex (1) 
writes table sybsystemprocs..sp_statistics_rset_002 
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysindexes