Stored procedure to define dafaults of all column of a table

[2969]

hi

if anybody can help me with the syntax of stored procedure where it will set default for each column of a given table to a specfied value.

 

[2969]

I found out the stored procedure but how do I identify if the field type is charactr or integer. This is what i am using in the stored procedure.

PROCEDURE DefaultsALL
USE qtk EXCLUSIVE
FOR i = 1 TO FCOUNT()
fieldname = FIELD(i)
ALTER TABLE Qtk ALTER COLUMN (fieldname) set Default ' '
ENDFOR
ENDPROC

 

[RickSchummer]

I think this will help you out...

lcDataType = VARTYPE(DBGETPROP("qtk." + fieldname,"FIELD","DefaultValue"))

_RAS

http://www.whitelightcomputing.com

VFP MVP

 

[2969]

I am looking for something like this to put in this stored procedure. Is somthing like this exists in stored procedure for fox pro. I am bery new to fox pro and this is the first time i am looking at stored procedures.


PROCEDURE DefaultsALL
USE qtk EXCLUSIVE
FOR i = 1 TO FCOUNT()
fieldname = FIELD(i)
fieldtype= ftype()'don't know if this exists
if fieldtype= Integer then
ALTER TABLE Qtk ALTER COLUMN (fieldname) set Default 0
elseif fieldtype= Char then
ALTER TABLE Qtk ALTER COLUMN (fieldname) set Default ''

ENDFOR
ENDPROC

 

[RickSchummer]

Replace the line with FType() with my code suggestion. Use the return value (one character describing the data type) in your IF...ELSE...IF structure, or better yet use a CASE structure. (see the Help file for the DO CASE command and the TYPE() function for all the possibilities of data types in VFP.


_RAS

http://www.whitelightcomputing.com

VFP MVP

 

[2969]

hi, i really apprecaite guidance here... I did what you suggested but when i execute it, i get an error "syntax error"

PROCEDURE DefaultsALL
USE qtk EXCLUSIVE
FOR i = 1 TO FCOUNT()
fieldname = FIELD(i)

do case TYPE(i)
case 'C'
ALTER TABLE Qtk ALTER COLUMN (fieldname) set default ''
case 'N'
ALTER TABLE Qtk ALTER COLUMN (fieldname) set default 0
ENDCASE
ENDFOR
ENDPROC

 

[2969]

hi, i got it work.. this is what I am using..thx for all the help

PROCEDURE DefaultALL
USE qtk EXCLUSIVE
FOR i = 1 TO FCOUNT()
fieldname = FIELD(i)

if TYPE('qtk.'+ fieldname) = 'C' then

ALTER TABLE Qtk ALTER COLUMN (fieldname) set default ''

ENDIF

ENDFOR

ENDPROC