function AlterColumnSQL($tabname, $flds) { $tabname = $this->TableName($tabname); $sql = array(); list($lines,$pkey) = $this->_GenFields($flds); foreach($lines as $v) { // get field name and type $arTmp = array(); $arTmp = split(" ", $v); $arCnt = count($arTmp); $fieldname = $arTmp[0]; $fieldtype = $arTmp[1]; $bHasDef = false; $bIsNull = true; for($i = 2; $i < $arCnt; $i++) { // get default value if( trim($arTmp[$i]) == "DEFAULT" ) { $bHasDef = true; if( $i+1 <= $arCnt ) { $field_def = $arTmp[$i+1]; } } // get nullability if( trim($arTmp[$i]) == "NOTNULL" ) $bIsNull = false; } // delete default value if exists if( $this->connection->IsConnected() ) { $oRs = $this->connection->Execute(' SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid WHERE object_name(so.parent_obj) = \'' . $tabname . '\' AND so.xtype = \'D\' AND sc.colid = ( SELECT colid FROM syscolumns WHERE id = object_id(\'' . $tabname . '\') AND name = \'' . $fieldname . '\' ) '); if( $oRs ) if(!$oRs->EOF) { $defName = $oRs->fields[0]; // Remove default value $sql[] = "ALTER TABLE $tabname DROP CONSTRAINT $defName"; } $oRs->Close(); }; if($bHasDef) { // change type and nulls if($bIsNull) $sql[] = "ALTER TABLE $tabname $this->alterCol $fieldname $fieldtype NOT NULL"; else $sql[] = "ALTER TABLE $tabname$this->alterCol $fieldname $fieldtype"; // Add default value $sql[] = "ALTER TABLE $tabname ADD CONSTRAINT def_" . $tabname . "_" . $fieldname . " DEFAULT " . $field_def . " FOR $fieldname"; } else { $sql[] = "ALTER TABLE $tabname $this->alterCol $v"; } } //print_r($sql); return $sql; }