#!/bin/ksh
###########################################################
#
# findcol database column
#
# returns any table or column within database that has some
# portion of column within its name
#
# Jack Parker 2002
#
###########################################################
echo "
select tabname[1,25], colname[1,25], coltype, collength
  from systables a, syscolumns b
 where a.tabid = b.tabid
   and (colname matches \"*$2*\" or tabname matches \"*$2*\");
" | dbaccess $1 - 2>/dev/null | tail +4l | grep -v ^$ | awk '
BEGIN {
dtp[0]="char"
dtp[1]="smallint"
dtp[2]="integer"
dtp[3]="float"
dtp[4]="smallfloat"
dtp[5]="decimal"
dtp[6]="serial"
dtp[7]="date"
dtp[8]="money"
dtp[9]="unknown"
dtp[10]="datetime"
dtp[11]="byte"
dtp[12]="text"
dtp[13]="varchar"
dtp[14]="interval"
dtp[15]="nchar"
dtp[16]="nvarchar"
dtp[17]="unk"
dtp[18]="unk"
dtp[19]="unk"
dtp[20]="unk"

datp[1] = "year"
int_start[1]=1
int_end[1]=5
datp[3] = "month"
int_start[3]=5
int_end[3]=7
datp[5] = "day"
int_start[5]=7
int_end[5]=9
datp[7] = "hour"
int_start[7]=9
int_end[7]=11
datp[9] = "minute"
int_start[9]=11
int_end[9]=13
datp[11] = "second"
int_start[11]=13
int_end[11]=15
datp[12] = "fraction(1)"
int_start[12]=15
int_end[12]=16
datp[13] = "fraction(2)"
int_start[13]=16
int_end[13]=17
datp[14] = "fraction(3)"
int_start[14]=17
int_end[14]=18
datp[15] = "fraction(4)"
int_start[15]=18
int_end[15]=19
datp[16] = "fraction(5)"
int_start[16]=19
int_end[16]=20
}
function fixnm(coll,tp)
{
   i = int(coll / 256)
   j = coll % 256
   if (tp == 0) {
	if (j > i ) strg=i
	else strg = sprintf("%s,%s",i,j)
	}
   else {
	if (i == 0) strg=j
	else strg = sprintf("%s,%s",j,i)
	}
   return strg
}
function fixdt(coll)
{
   i = coll % 16 + 1
   j = int((coll % 256) / 16 ) + 1
   k = int(coll / 256)
   ln = int_end[i] - int_start[j]
   ln = k - ln
   if (ln == 0 || j > 11) { strg = sprintf("%s to %s", datp[j], datp[i]) }
   else {
	k int_end[j] - int_start[j]
	k = k + ln
	strg = sprintf("%s (%d) to %s", datp[j], k, datp[i])
	}
   return strg
}
{
# Tab, colname, type, length
outstrg=""
tabname=$1
colname=$2
nonull=$3/256
coltype=$3%256
collength=$4
outstrg=dtp[coltype]
if (coltype == 0) {outstrg=sprintf("%s(%s)",outstrg, collength)}
if (coltype == 5 || coltype == 8 ) {
		outstrg=sprintf("%s(%s)",outstrg,fixnm(collength,0)) }
if (coltype == 13 ) {outstrg=sprintf("%s(%s)",outstrg,fixnm(collength,1)) }
if (coltype == 10 || coltype == 14 ) {
		outstrg=sprintf("%s(%s)",outstrg,fixdt(collength)) }
printf("%-20s %-20s %-25s\n", tabname, colname, outstrg)
}'