calculating memo flds 1

[sqlpro]

Hi friends

we r in te processing migrating our vfp data to sql server. and in the process we need check memo fields
and find out if it is necessary to make them as text datatype in sql server or keep them just as varchars.
i need to start examining the memo fields in each table to determine those which are of genuine unlimited textual length, and those that should be of a more correct data type?
we decided that if a column contains more than say 2000chars of data, we make as text datatype.
so i need make a report containg each memo field and its data length.
whats the best way of doing it?
i know that i can scan thr the tables and can find out Len(fld1)
i'm wondering is there simple way/better way
Thanks

cheers

cheers

 

[craigsboyd]

while this is a database related question and as such it is off-topic here, I think you can see I have given up hope that off-topic posts will ever be stopped...

so here's some code I just wrote for you that will give you the content length of all memo fields of all the tables in a directory that you select...

LOCAL lcTablesPath, lcFile, lcMask, lnCounter, lnTotalFields, lcFieldName, lnContentLength
lcTablesPath = ADDBS(GETDIR("", "Tables Directory:", "TABLES FOLDER?"))		
CREATE CURSOR MemoInfo (tablename c(30), fieldname c(30), length I)
SET CPDIALOG OFF
SET DEFAULT TO (lcTablesPath)
SET TALK OFF					
lcMask = "*.dbf"
lcFile = SYS(2000,lcMask)
DO WHILE !EMPTY(lcFile)
	SELECT 0
	USE (lcFile) ALIAS "ATABLE"
	lnTotalFields = AFIELDS(aryFields)  && Create array.
	FOR lnCounter = 1 TO lnTotalFields
	   IF ALLTRIM(aryFields(lnCounter,2)) == "M"
	   		lcfieldname = aryFields(lnCounter,1)
	   		CALCULATE MAX(LEN(&lcfieldname)) TO lnContentLength
	   		INSERT INTO MemoInfo(tablename, fieldname, length) VALUES (JUSTSTEM(lcFile), lcFieldname, lnContentLength)
	   ENDIF
	ENDFOR
	USE IN SELECT("ATABLE")
	lcFile = SYS(2000,lcMask,1)
ENDDO
SELECT ("MemoInfo")
BROWSE

_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[sqlpro]

Thats very useful Craig.
Thank u very much

cheers

 

[baltman]

The text import fuction I posted in thread184-807978 could be adapted to identify the width, decimals and data types of memo fields as well... which could be useful if some memo fields have only a date or numbers.

Brian

 

[sqlpro]

Thanks Brian
I'll check it out

cheers

 

[sqlpro]

Hi friends
i have few more questions on this.. like

what percentage of rows have anything in their memo fields, the average length per field,
and the total number of characters per memo


cheers

 

[sqlpro]

I got it for average length like

CALCULATE avg(LEN(&lcfieldname)) TO lnContentLength

but wondering abt other 2 !


cheers

 

[baltman]

percentage of rows have anything in their memo fields:
sum(1) to lnCount for !empty(MyField)
?lnCount/reccount()*100

total number of characters per memo:
What do you mean? Length? Characters used? I assume you mean length, and you should have that already. If you mean "ABCAA" has '3', then you'd need to use occurs().

Brian

 

[sqlpro]

Thanks Brian
I get the Idea

cheers

 

[sqlpro]

Hi Brian
Just to let u know how i am doing

lnreccount=RECCOUNT()		&& total records
SELECT MAX(LEN(ALLTRIM(&lcfldname))) as maxlen,avg(LEN(ALLTRIM(&lcfldname))) as avgl ;
FROM &lctabname INTO CURSOR curTemp NOFILTER 

nlen=curTemp.maxlen		&& max len
lnAvgMemoLen=INT(curTemp.avgl) && average memo max len and USE IN SELECT('curTemp')	&& close cursor

SELECT &lctabname
sum(1) to lnLen for !EMPTY(ALLTRIM(&lcfldname))	&& length lnPerRows=INT(lnLen/lnreccount*100) && percentage of rows 

*!* finally insert data into cursor
INSERT INTO MemoInfo(tablename,memofield,MaxDatasize,Records,AvgMemoLen,PerRows) ;VALUES ;
(lctabname, lcfldname, nlen,lnreccount,lnAvgMemoLen,lnPerRows)

it works beautifully
thanks

cheers