DatabaseProcApplicationCreatedLinks
sybsystemprocssp_objectsegment  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/lock */
4     
5     /*
6     ** Messages for "sp_objectsegment"      17714
7     **
8     ** LOCALIZE the following:
9     **
10    **      select Data_located_on_segment = s.name, When_created = crdate
11    **
12    ** Data_located_on_segment
13    ** When_created
14    **
15    ** 17714, "not applicable" 
16    */
17    
18    /*
19    ** IMPORTANT NOTE:
20    ** This stored procedure uses the built-in function object_id() in the
21    ** where clause of a select query. If you intend to change this query
22    ** or use the object_id() or db_id() builtin in this procedure, please read the
23    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
24    ** pertaining to object-id's and db-id's outlined there, are followed.
25    */
26    
27    create or replace procedure sp_objectsegment
28        @objname varchar(767) /* table name */
29    as
30        declare @msg varchar(30) -- Keep length short so that message comes
31        -- out nicely formatted. No need for 1024 here.
32    
33    
34        if @@trancount = 0
35        begin
36            set chained off
37        end
38    
39        set transaction isolation level 1
40    
41        /* 
42        ** Print out the create date for each partition of the object 
43        ** and it's data segment, if applicable.
44        */
45    
46        /*
47        **  Objects have the following value for sysstat & 15:
48        **			15 is the max value for object type.
49        **	0 - any/illegal object
50        **	1 - system table
51        **	2 - view
52        **	3 - user table
53        **	4 - sproc
54        **	5 - NOT UNUSED
55        **	6 - default
56        **	7 - rule
57        **	8 - trigger
58        **	9 - referential constraint
59        **	10 - check constraint
60        **	11 - Extended type
61        **	12 - stored function
62        **	13 - computed column
63        **	14 - Partition condition
64        */
65        if exists (select *
66                from sysobjects
67                where id = object_id(@objname)
68                    and sysstat & 15 in (1, 3))
69        begin
70            select Partition_name = p.name,
71                Data_located_on_segment = s.name,
72                When_created = p.crdate
73            into #segresult
74            from syssegments s, syspartitions p
75            where p.id = object_id(@objname)
76                and p.indid < 2
77                and p.segment = s.segment
78    
79            exec sp_autoformat @fulltabname = '#segresult',
80                @orderby = 'order by 1'
81        end
82    
83        /*
84        **  It's not a table so segment is not applicable.
85        */
86        else
87        begin
88            /*
89            ** 17714, "not applicable" 
90            */
91            exec sp_getmessage 17714, @msg output
92            select Data_located_on_segment = @msg,
93                When_created = o.crdate
94            from sysobjects o
95            where o.id = object_id(@objname)
96        end
97    
98        return (0)
99    
100   


exec sp_procxmode 'sp_objectsegment', 'AnyMode'
go

Grant Execute on sp_objectsegment to public
go
RESULT SETS
sp_objectsegment_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 77
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 76
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 67
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 75
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 95
 MGTP 3 Grant to public sybsystemprocs..sp_objectsegment  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syspartitions  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MNER 3 No Error Check should check @@error after select into 70
 MNER 3 No Error Check should check return value of exec 79
 MNER 3 No Error Check should check return value of exec 91
 MUCO 3 Useless Code Useless Brackets 98
 QCRS 3 Conditional Result Set 92
 QCTC 3 Conditional Table Creation 70
 QISO 3 Set isolation level 39
 QNAJ 3 Not using ANSI Inner Join 74
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
75
 MRST 2 Result Set Marker 92
 MSUB 2 Subquery Marker 65
 MTR1 2 Metrics: Comments Ratio Comments: 56% 27
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 6dec - 1exi + 2 27
 MTR3 2 Metrics: Query Complexity Complexity: 24 27
 PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 70

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#segresult (1) 
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
writes table sybsystemprocs..sp_objectsegment_rset_001 
reads table sybsystemprocs..syspartitions  
reads table sybsystemprocs..syssegments  
calls proc sybsystemprocs..sp_autoformat  
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   writes table sybsystemprocs..sp_autoformat_rset_002