| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_mon_gen_columnlist | | 31 Aug 14 | Defects Dependencies |
1 2 create 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
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysobjects (1)  reads table master..syscolumns (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 |
