DatabaseProcApplicationCreatedLinks
sybsystemprocssp_mon_gen_columnlist  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_mon_gen_columnlist(
3         @montable varchar(30)
4         , @sellist varchar(1500) output
5         , @gen_cols varchar(768) output
6     ) as
7         begin
8             declare @retval int
9                 , @colname varchar(30)
10                , @col_dbname varchar(30)
11                , @col_sep char(1)
12                , @id_start tinyint
13                , @id_length tinyint
14                , @mon_tabid int
15    
16            select @retval = 1 -- Assume failure
17                , @id_length = datalength('ID')
18                , @col_sep = NULL
19    
20                , @mon_tabid = isnull((select o.id
21                        from master.dbo.sysobjects o
22                        where o.name = @montable
23                            and o.uid = user_id('dbo')), 0)
24    
25            -- For the rare case that the specified monitor table does not exist
26            -- in masterdb, trap an error and return.
27            --
28            if (@mon_tabid = 0)
29            begin
30                select @montable = 'dbo.' + @montable
31                raiserror 17870, @montable, 'master', ""
32                return 1
33            end
34    
35            declare colcur cursor for
36            select c.name from master.dbo.syscolumns c
37            where c.id = @mon_tabid
38            order by c.colid asc
39            for read only
40    
41            open colcur
42    
43            while (1 = 1)
44            begin -- {
45                fetch colcur into @colname
46                if (@@error != 0)
47                begin
48                    goto end_cursor
49                end
50                else if (@@sqlstatus != 0)
51                    break
52    
53                -- Keep them short, to avoid row-size warnings, in the generated
54                -- SQL fragment.
55                -- (dbname is still varchar(30) in 15.0x also.)
56                --
57                if (@colname like '%DBName%')
58                begin
59                    select @sellist = @sellist + @col_sep + @colname
60                end
61    
62                else if (@colname like '%Name%')
63                begin
64                    select @sellist = @sellist
65                        + @col_sep
66                        + @colname
67                        + '=convert(varchar(255),'
68                        + @colname
69                        + ')'
70                end
71    
72                else
73                begin -- {
74                    select @sellist = @sellist + @col_sep + @colname
75    
76                    -- Add equivalent '*DBName' column if this column is
77                    -- some '%DBID' column.
78                    --
79                    if (@colname like '%DBID')
80                    begin -- {
81    
82                        -- Convert column to '%DBName' column.
83                        --
84                        select @id_start = (datalength(@colname)
85                            - @id_length) + 1
86                        select @col_dbname = stuff(@colname,
87                                @id_start,
88                                @id_length,
89                                'Name')
90    
91                        -- Append the equivalent dbname column if it
92                        -- does not already exist for this table.
93                        --
94                        if not exists (select 1
95                                from master.dbo.syscolumns
96                                where id = @mon_tabid
97                                    and name = @col_dbname)
98                        begin
99                            /*
100                           + '=convert(varchar(30),db_name(case (select 1 from master.dbo.sysdatabases where dbid=m.'
101                           + @colname
102                           + ') when 1 then '
103                           + @colname
104                           + ' else NULL end'
105                           */
106   
107                           select @gen_cols = @gen_cols
108                               + ','
109                               + @col_dbname
110                               + '=convert(varchar(30),db_name('
111                               + @colname
112                               + '))'
113                       end
114                   end -- }
115   
116                   else if (@montable = 'monDeadLock')
117                   begin
118                       select @gen_cols = @gen_cols
119                           + case @colname
120                               when 'InstanceID'
121                               then ',InstanceName=convert(varchar(30) NULL, instance_name(InstanceID))'
122                               else NULL
123                           end
124                   end
125               end -- }
126   
127               -- Now that we've got the 1st col, reset the column-separator
128               -- to a valid value.
129               --
130               select @col_sep = ','
131   
132           end -- }
133   
134           select @retval = 0
135   
136   end_cursor:
137           close colcur
138           deallocate cursor colcur
139   
140           return @retval
141       end -- }
142   

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 31
 MAW1 3 Warning message on %name% master..sysobjects.id: Warning message on sysobjects 20
 MAW1 3 Warning message on %name% master..syscolumns.id: Warning message on syscolumns 37
 MAW1 3 Warning message on %name% master..syscolumns.id: Warning message on syscolumns 96
 MGTP 3 Grant to public master..syscolumns  
 MGTP 3 Grant to public master..sysobjects  
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Begin-End Pair 7
 MUCO 3 Useless Code Useless Brackets 28
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 116
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
37
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
96
 CRDO 2 Read Only Cursor Marker (has for read only clause) 36
 MSUB 2 Subquery Marker 20
 MSUB 2 Subquery Marker 94
 MTR1 2 Metrics: Comments Ratio Comments: 23% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 12dec - 2exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 50 2

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syscolumns (1)  
reads table master..sysobjects (1)  

CALLERS
called by proc sybsystemprocs..sp_mon_archive_genSQL  
   called by proc sybsystemprocs..sp_mon_archive_monTable  
      called by proc sybsystemprocs..sp_mon_archive_deadlock  
   called by proc sybsystemprocs..sp_monitor_deadlock  
      called by proc sybsystemprocs..sp_monitor