#! /bin/ksh

#----------------------------------------------------------#
# PROGRAM: sybgrep                                         #
#                                                          #
# CREATED by Vijay Mitra  27 Jun 2000                      #
#                                                          #
# DESCRIPTION: Sybase server details of an Environment.    #
#                                                          #
# NB: Need to change "Variables" depending on environment. #
#----------------------------------------------------------#


FILE=/tmp/summ.txt

determine_echo_type()
{
        zzkj=`echo '\c'`

        if [ -z "$zzkj" ]
        then
                echo1=
                echo2='\c'
        else
                echo1="-n "
                echo2=
        fi
}

###########################################################################
###########################################################################

nonlecho()
{
        echo $echo1 "$@"$echo2
}

###########################################################################
###########################################################################

determine_echo_type
echo "\n####################################################"
echo "sybgrep a simple utility to list details entered         "
echo "PROGRAMMER : Vijay Mitra "
echo "####################################################\n"

## Take Input 

if [ $# -eq 0 ]
then
   echo "\nUsage : sybgrep -o[objName] | -c[colName] | -t[type<U/P/V/TR/S>]"
   echo "\nOPTIONS"
   echo "\n\t-o     Table name or art of table name e.g party "
   echo "\n\t-c     Any particular column name e.g party_long_name_p2k"
   echo "\n\t-t     Type of object wish to find in "
   echo "\t       U -tables, P -stored procs, V -views, TR -triggers, S -system tables"
   echo "\nEXAMPLES"
   echo "\n\t1.  sybgrep -o party -t U\n\textracts all the tables which have party in their name"
   echo "\n\t2.  sybgrep -o party -c party\n\textracts all the columns which have party in "
   echo "\ttheir name from the tables which have party in their name\n"
   exit 0
fi

echo $* | sed 's/ //g' | awk -F- '{for (x=1;x<= NF;x++) print $x}' > /tmp/tmp

OBJ_NAME=`grep '^o' /tmp/tmp |cut -c2-`
COL_NAME=`grep '^c' /tmp/tmp |cut -c2-`
TYPE=`grep '^t' /tmp/tmp |cut -c2-`

rm /tmp/tmp

#echo $TYPE |egrep "P|S|TR|U|V" > /dev/null
#RET=$?

#if [ $TYPE -ne '' ]; then
#echo hi
   #if [ $RET -eq 1 ]; then
      #echo "Error : Wrong option for -t "
      #exit 0
   #fi
#fi
      
## Start the SYBASE acts

ENV=`echo $WKENV | cut -f2 -d'-'|cut -c3-`

if [ ! $ENV ]; then
   nonlecho "Supply the DB Prefix viz. <trade/control/account/dwz/meta/opgs etc> : "
   read  ENV
fi


STOPLIGHT=`echo $ENV | cut -c1`
case $STOPLIGHT in
 a) DB=ACCOUNTS_$DB_PFIX ;;
 c) DB=CTRL_$DB_PFIX ;;
 d) DB=DWZ_$DB_PFIX ;;
 m) DB=META_$DB_PFIX ;;
 o) DB=HUB_$DB_PFIX ;;
 t) DB=TRADE_$DB_PFIX ;;
esac

echo "\nWorking in $DB environment... Please wait\n"
isql -S$DSQUERY -U$DB_USER -P$DB_PASSWD -w1000 -o$FILE << EOF

USE $DB
GO

    CREATE TABLE #results
    (
         Obj_name     varchar(50),
         Column_name  varchar(50),
         DataType     char(45),
         Nulls        char(10)
    )

    DECLARE @objName char(50),
        @msg  char(100),
        @colName char(30),
        @dataType  char(25),
        @Nulls char(10),
        @type char(2)

    SET NOCOUNT ON

    IF '$OBJ_NAME' = '' 
       SELECT @objName = NULL
    ELSE SELECT @objName = '$OBJ_NAME'
    IF '$COL_NAME' = '' 
       SELECT @colName = NULL
    ELSE SELECT @colName = '$COL_NAME'
    IF  '$TYPE' = '' 
       SELECT @type = NULL
    ELSE SELECT @type = '$TYPE'

/** If tablename = Null and Column = Specified **/
    IF (@objName = NULL AND @colName != NULL )
    BEGIN
      INSERT INTO #results
      SELECT Obj_name  = ISNULL(O.name,'NULL'),
           Column_name = ISNULL(C.name, 'NULL'),
           DataType = CONVERT(varchar(20),T.name)+'('+CONVERT(varchar(10),C.length)+')',
           Nulls = CONVERT(bit, (C.status & 8))
      FROM syscolumns C, systypes T ,sysobjects O
      WHERE   C.usertype *= T.usertype
      AND C.name like '%'+RTRIM(@colName)+'%'
      AND O.id = C.id
      AND O.type = ISNULL(@type,O.type)
    END
    ELSE
/** If tablename = Specified and Column = Null **/
/** Behave like a sp_help tablename **/
    IF (@objName != NULL AND @colName = NULL)
    BEGIN
      INSERT INTO #results
      SELECT Obj_name = O.name,
           Column_name = ISNULL(C.name, 'NULL'),
           DataType = CONVERT(varchar(20),T.name)+'('+CONVERT(varchar(10),C.length)+')',
           Nulls = CONVERT(bit, (C.status & 8))
      FROM syscolumns C, systypes T ,sysobjects O 
      WHERE  C.usertype *= T.usertype
      AND O.id = C.id
      AND O.type = ISNULL(@type,O.type)
      AND O.name LIKE '%'+RTRIM(@objName)+'%'
    END
    ELSE
/** If tablename = Specified and Column = Specified **/
    IF (@objName != NULL AND @colName != NULL)
    BEGIN
      INSERT INTO #results
      SELECT Obj_name = O.name,
           Column_name = ISNULL(C.name, 'NULL'),
           DataType = CONVERT(varchar(20),T.name)+'('+CONVERT(varchar(10),C.length)+')',
           Nulls = CONVERT(bit, (C.status & 8))
      FROM syscolumns C, systypes T ,sysobjects O
      WHERE  C.usertype *= T.usertype
      AND C.name LIKE '%'+RTRIM(@colName)+'%'
      AND O.id = C.id
      AND O.type = ISNULL(@type,O.type)
      AND O.name LIKE '%'+RTRIM(@objName)+'%'
    END

/** If tablename = NULL and Column = NULL and Type = Specified **/
/** List all of the specified type **/

    IF (@objName = NULL AND @colName = NULL AND @type != NULL)
    BEGIN
      PRINT "=========================================================================="
      SELECT Obj_name = O.name
      FROM sysobjects O
      WHERE  O.type = @type
      ORDER BY O.name

      PRINT "=========================================================================="
      RETURN
    END


    UPDATE #results
    SET Nulls = 'NOT NULL'
    WHERE Nulls = '0'

    UPDATE #results
    SET Nulls = 'NULL'
    WHERE Nulls = '1'

    SELECT DISTINCT Obj_name INTO #objects FROM #results

    PRINT "=========================================================================="
    WHILE EXISTS (SELECT * FROM #objects)
    BEGIN
       SET ROWCOUNT 1
       SELECT @objName = Obj_name FROM #objects
       SELECT @msg = "Object : "  + @objName
       PRINT  @msg
       PRINT  ""
       DELETE #objects WHERE Obj_name = @objName
       WHILE EXISTS (SELECT * FROM #results WHERE  Obj_name = @objName)
       BEGIN
          SELECT @colName = Column_name,
             @dataType = DataType,
             @Nulls = Nulls FROM #results
          WHERE  Obj_name = @objName
          DELETE #results WHERE  Obj_name = @objName
          PRINT "%1! %2! %3! ",@colName,@dataType,@Nulls
       END
       PRINT "=========================================================================="
    END
  
    DROP TABLE #results
    DROP TABLE #objects
GO

EOF

cat $FILE

rm $FILE