| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_drv_column_default | | 31 Aug 14 | Defects Dependencies |
1 /** SECTION END: CLEANUP **/ 2 3 create procedure sp_drv_column_default 4 /* Don't delete the following line. It is the checkpoint for sed */ 5 /* Server dependent stored procedure add here ADDPOINT_COL_DEFAULT */ 6 (@obj_id int, @default_value varchar(1024) output) 7 as 8 declare @text_count int 9 declare @default_holder varchar(255) 10 declare @rownum int 11 declare @create_default_starts int 12 declare @default_starts int 13 declare @actual_default_starts int 14 declare @as_starts int 15 declare @length int 16 declare @check_case_one int 17 declare @check_last_char int 18 declare @linefeed_char char(2) 19 declare @last_char char(2) 20 21 /* make sure @default_value starts out as empty */ 22 select @default_value = null 23 24 /* initialize @check_case_one to false (0) */ 25 select @check_case_one = 0 26 27 /* initialize @check_last_char to false (0) */ 28 select @check_last_char = 0 29 30 /* initialize the @linefeed_char variable to linefeed */ 31 select @linefeed_char = char(10) 32 33 /* Find out how many rows there are in syscomments defining the 34 default. If there are none, then we return a null*/ 35 select @text_count = count(*) from syscomments 36 where id = @obj_id 37 38 if @text_count = 0 39 begin 40 return 0 41 end 42 43 /* See if the object is hidden (SYSCOM_TEXT_HIDDEN will be set). 44 If it is, best we can do is return null*/ 45 if exists (select 1 from syscomments where (status & 1 = 1) 46 and id = @obj_id) 47 begin 48 return 0 49 end 50 51 select @rownum = 1 52 declare default_value_cursor cursor for 53 select text from syscomments where id = @obj_id 54 order by number, colid2, colid 55 56 open default_value_cursor 57 58 fetch default_value_cursor into @default_holder 59 60 while (@@sqlstatus = 0) 61 begin 62 63 if @rownum = 1 64 begin 65 /* find the default value 66 ** Note that ASE stores default values in more than one way: 67 ** 1. If a client declares the column default value in the 68 ** table definition, ASE will store the word DEFAULT (in 69 ** all caps) followed by the default value, exactly as the 70 ** user entered it (meaning it will include quotes, if the 71 ** value was a string constant). This DEFAULT word will 72 ** be in all caps even if the user did something like this: 73 ** create table foo (col1 varchar (10) DeFaULT 'bar') 74 ** 2. If a client does sp_bindefault to bind a default to 75 ** a column, ASE will include the text of the create default 76 ** command, as entered. So, if the client did the following: 77 ** create DeFAULt foo aS 'bar' 78 ** that is exactly what ASE will place in the text column 79 ** of syscomments. 80 ** In this case, too, we have to be careful because ASE 81 ** will sometimes include a newline character 82 ** at the end of the create default statement. This 83 ** can happen if a client uses C isql to type in the 84 ** create default command (if it comes in through java, then 85 ** the newline and null are not present). 86 ** Because of this, we have to be careful when trying to parse out 87 ** the default value. */ 88 89 select @length = char_length(@default_holder) 90 select @create_default_starts = 91 charindex('create default', lower(@default_holder)) 92 select @as_starts = charindex(' as ', lower(@default_holder)) 93 select @default_starts = charindex('DEFAULT', @default_holder) 94 95 if (@create_default_starts != 0 and @as_starts != 0) 96 begin 97 98 /* If we get here, then we likely have case (2) above. 99 ** However, it's still possible that the client did something 100 ** like this: 101 ** create table foo (col1 varchar (20) default 102 ** 'create default foo as bar') 103 ** The following if block accounts for that possibility */ 104 105 if (@default_starts != 0 and 106 @default_starts < @create_default_starts) 107 begin 108 select @check_case_one = 1 109 end 110 else 111 begin 112 select @actual_default_starts = @as_starts + 4 113 select @check_last_char = 1 114 115 /* set @default_starts to 0 so we don't fall into the 116 ** next if block. This is important because we would 117 ** fall into the next if block if a client had used the 118 ** following sql: 119 ** CREATE DEFAULT foo as 'bar' */ 120 select @default_starts = 0 121 end 122 end 123 124 if (@default_starts != 0 or @check_case_one != 0) 125 /* If we get here, then we have case (1) above */ 126 127 select @actual_default_starts = @default_starts + 7 128 129 /* The ltrim removes any left-side blanks, because ASE appears 130 ** to insert several blanks between the word DEFAULT and the 131 ** start of the default vale */ 132 133 select @default_holder = 134 ltrim(substring 135 (@default_holder, @actual_default_starts, @length)) 136 137 end 138 139 select @default_value = @default_value + @default_holder 140 select @rownum = @rownum + 1 141 142 fetch default_value_cursor into @default_holder 143 144 end /* while loop */ 145 146 close default_value_cursor 147 148 /* trim off any right-side blanks */ 149 select @default_value = rtrim(@default_value) 150 151 /* trim off the newline and null characters, if they're the last 152 ** two characters in what remains */ 153 if (@check_last_char = 1) 154 begin 155 156 select @length = char_length(@default_value) 157 select @last_char = substring(@default_value, @length, 1) 158 if (@last_char = @linefeed_char) 159 select @default_value = substring(@default_value, 1, (@length - 1)) 160 end 161 162 return 0 163 164 165
exec sp_procxmode 'sp_drv_column_default', 'AnyMode' go Grant Execute on sp_drv_column_default to public go
| DEFECTS | |
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause default_value_cursor | 53 |
| MGTP 3 Grant to public sybsystemprocs..sp_drv_column_default | |
| MGTP 3 Grant to public sybsystemprocs..syscomments | |
| MUCO 3 Useless Code Useless Brackets in create proc | 6 |
| MUCO 3 Useless Code Useless Brackets | 60 |
| MUCO 3 Useless Code Useless Brackets | 95 |
| MUCO 3 Useless Code Useless Brackets | 105 |
| MUCO 3 Useless Code Useless Brackets | 124 |
| MUCO 3 Useless Code Useless Brackets | 153 |
| MUCO 3 Useless Code Useless Brackets | 158 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered (id, number, colid2, colid, texttype) Intersection: {id} | 36 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered (id, number, colid2, colid, texttype) Intersection: {id} | 45 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered (id, number, colid2, colid, texttype) Intersection: {id} | 53 |
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) | 53 |
| MSUB 2 Subquery Marker | 45 |
| MTR1 2 Metrics: Comments Ratio Comments: 49% | 3 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 13dec - 3exi + 2 | 3 |
| MTR3 2 Metrics: Query Complexity Complexity: 71 | 3 |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..syscomments CALLERS called by proc sybsystemprocs..sp_odbc_columns called by proc sybsystemprocs..sp_jdbc_columns  |