Database	Proc	Application	Created	Links
sybsystemprocs	sp_objectsegment		31 Aug 14	Defects 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 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
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: 62%	27
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 5dec - 1exi + 2	27
MTR3 2 Metrics: Query Complexity Complexity: 24	27
PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0	70