在unix下如何获取表结构及视图、过程、触发器

大头虎

在windows下通过sybase Central 或第三方软件如DBA等很容易获得表结构。在unix下也可以通过ddlgen获取（不过我从没有使用过ddlgen，没有安装过）

如何在unix下获取表结构呢，我写了一个脚本在hp9000下调试通过。
现在贴出，请大家指正！如果有一二可取之处，也请大家叫个好！^_^

大头虎

#!/bin/sh
#create by tANGzHI 2004-05-14 , get table's ddl from sybase

#usage
USAGE="usage: "`basename $0`" [database.owner.]OBJECT [-U username] [-P password] [-S server] "

if [ $# -lt 1 ];then
    echo $USAGE
    exit 100
fi

#get options
OBJECT_NAME=$1
shift
opt=$*
passwdflag=""
while getopts U:S: opt 2>; /dev/null
do
    case $opt in
    U)
        username="$OPTARG"
        ;;
    P)
        password="$OPTARG"
        passwdflag="1"
        ;;
    S)
        server="$OPTARG"
        ;;
    *)
        echo $USAGE
        echo "ERROR:invalid option!"
        exit 100
        ;;
    esac
done
if [ -z "$passwdflag" ];then
echo "password:\c"
stty -echo
read password
stty echo
echo ""
fi

#judge connect
echo "select '|ok|'\ngo"|isql -S $server -U $username -P $password >; .result
ConnectOK=`cat .result |grep "|ok|"|wc -l`
if [ $ConnectOK -ne 1 ]; then
    echo $USAGE
    echo "ERROR:Login fail!\n      The attempt to connect to the server failed."
    rm -f .result
    exit 100
fi

#analyse OBJECT
NUM=`echo $OBJECT_NAME|awk -F"." '{print NF}'`
if [ $NUM -ne 1 -a $NUM -ne 3 ];then
    echo $USAGE
    rm -f .result
    exit 101
fi
if [ $NUM -eq 1 ];then
    DATABASE="master"
    OWNER="dbo"
    OBJECT=$OBJECT_NAME
else
    DATABASE=`echo $OBJECT_NAME|awk -F"." '{print $1}'`
    OWNER=`echo $OBJECT_NAME|awk -F"." '{print $2}'`
    OBJECT=`echo $OBJECT_NAME|awk -F"." '{print $3}'`
    if [ -z "$DATABASE" ];then
        echo $USAGE
        rm -f .result
        exit 102
    fi
    if [ -z "$OWNER" ];then
        OWNER="dbo"
    fi
    if [ -z "$OBJECT" ];then
        echo $USAGE
        rm -f .result
        exit 103
    fi
fi
OBJECT_NAME=$DATABASE"."$OWNER"."$OBJECT

#check up object
statement="use master\ngo\nselect '|ok|' from sysdatabases where name='"$DATABASE"'\ngo"
echo $statement|isql -S $server -U $username -P $password >; .result
DatabaseExists=`cat .result|grep "|ok|" |wc -l`
if [ DatabaseExists -ne 1 ];then
    echo $USAGE
    echo "ERRORataBase "$DATABASE" Not Exists!"
    rm -f .result
    exit 110
fi
statement="use "$DATABASE"\ngo\nSelect '|ok|'+convert(varchar,uid) from sysusers where name ='"$OWNER"'\ngo"
echo $statement|isql -S $server -U $username -P $password >; .result
OwnerExists=`cat .result|grep "|ok|" |wc -l`
if [ DatabaseExists -ne 1 ];then
    echo $USAGE
    echo "ERROR:"$OBJECT_NAME" Not Exists!"
    rm -f .result
    exit 111
fi
uid=`cat .result|grep "|ok|"|awk -F"|" '{print $3 }'`
statement="use "$DATABASE"\ngo\nSelect '|ok|'+type from sysobjects where name ='"$OBJECT"' and uid="$uid"\ngo"
echo $statement|isql -S $server -U $username -P $password >; .result
ObjectExists=`cat .result|grep "|ok|"|wc -l`
if [ ObjectExists -ne 1 ];then
    echo $USAGE
    echo "ERROR:"$OBJECT_NAME" Not Exists!"
    rm -f .result
    exit 112
fi

TYPE=`cat .result|grep "|ok|"|awk -F"|" '{print $3}'`
#rtrim TYPE
TYPE=`echo $TYPE`  

#SQL comments
SqlComments="use "$DATABASE"\ngo\nselect isnull(c.name, 'NULL')+'|'+t.name+
       case when t.name in ('decimal','decimaln','numeric','numericn') then '('+convert(varchar,c.prec)+','+convert(varchar,c.scale)+')'
            when t.name in ('bit','datetime','extended type','image','int','money','real','smalldatetime','smallint','smallmoney','sysname','text','timestamp','tinyint') then null
            when t.name in ('nchar','nvarchar') then '('+convert(varchar,c.length/@@ncharsize)+')'
            else '('+convert(varchar,c.length)+')' end+'|'+
       m.text+
       case when convert(bit, (c.status & 0x80))=1 then 'IDENTITY' when convert(bit, (c.status & ) = 1 then 'NULL' ELSE 'NOT NULL'  end+'|'
from syscolumns c, systypes t,syscomments m
                where c.id = object_id('"$OWNER"."$OBJECT"')
                        and c.usertype *= t.usertype
            and c.cdefault*=m.id
order by c.colid asc \ngo"
echo $SqlComments>;.sql

#
GetIndCol()
{
        indexid=$1;icnt=$2
        Keys="";i=1
        while [ $i -le $icnt ]
        do
        echo "use "$DATABASE"\ngo\nselect '|ok|'+index_col('"$OWNER"."$OBJECT"',"$indexid","$i"+'|'+index_colorder('"$OWNER"."$OBJECT"',"$indexid","$i"+'|' \ngo"|isql -S $server -U $username -P $password >; .result
        KeyExists=`cat .result|grep "|ok|"|awk -F"|" '{print $3}'`
    if [ -z "$KeyExists" ];then
        break
    else
        if [ $i -gt 1 ];then
            Keys=$Keys","
        fi
        Key=`cat .result|grep "|ok|"|awk -F"|" '{print $3}'`
        IsDesc=`cat .result|grep "|ok|"|awk -F"|" '{print $4}'`
        if [ $IsDesc = "DESC" ];then
            Key=$Key"  DESC"
        fi
        Keys="$Keys$Key"
    fi
    i=`expr $i + 1`
    done
    echo $Keys
}

#get ddl
case $TYPE in
  S|U)
      Indid=0
      #table
      printf "--%s\nUSE %s\nGO\nCREATE TABLE %s.%s\n(" $OBJECT_NAME $DATABASE $OWNER $OBJECT
      echo $SqlComments|isql -S $server -U $username -P $password >; .result
      cat .result|grep "|"|sed 's/| *$/|/g'|sed 's/^        / /g'|grep -v "^$"|awk -F"|" '{printf "\n%-20.18s%-20.18s%-s,",$1,$2,$3}'
      #PrimaryKey
      echo "use "$DATABASE"\ngo\nselect '|ok|'+name+'|'+convert(varchar,indid)+'|'+convert(varchar,(status2 & 512))+'|'+convert(varchar,keycnt)+'|' from sysindexes where id=object_id('"$OWNER"."$OBJECT"') and indid >; 0 and (status & 2048 = 204 and (status2 & 2 = 2)\ngo"|isql -S $server -U $username -P $password >; .result
      PrimaryKeyExists=`cat .result|grep "|ok|"|wc -l`
      if [ $PrimaryKeyExists -eq 1 ];then
          PrimaryKeyName=`cat .result|grep "|ok|"|awk -F"|" '{print $3}'`
          Indid=`cat .result|grep "|ok|"|awk -F"|" '{print $4}'`
          Keycnt=`cat .result|grep "|ok|"|awk -F"|" '{print $6}'`
          if [ $Indid -eq 1 ];then
              ClustInfo="CLUSTERED"
          else
              Status2=`cat .result|grep "|ok|"|awk -F"|" '{print $5}'`
              if [ $Status2 -eq 512 ];then
                  ClustInfo="CLUSTERED"
              else
                  ClustInfo="NONCLUSTERED"
              fi
          fi
          IndexCols=`GetIndCol $Indid $Keycnt`
          printf "\n  CONSTRAINT %s\n  PRIMARY KEY %s ( %s )" $PrimaryKeyName $ClustInfo $IndexCols
      else
          printf "\b "
      fi
      printf "\n)\nGO\n"
      #index
      echo "use "$DATABASE"\ngo\nselect '|ok|'+convert(varchar,max(indid)) from sysindexes where id=object_id('"$OWNER"."$OBJECT"') and indid >; 0 and status & 2048 != 2048 \ngo"|isql -S $server -U $username -P $password >; .result
      IndexExists=`cat .result|grep "|ok|"|wc -l`
      if [ $IndexExists -eq 1 ];then
          Maxindid=`cat .result|grep "|ok|"|awk -F"|" '{print $3}'`
          Maxindid=`echo $Maxindid`
          if [ -z "$Maxindid" ];then
              exit 0
          fi
          Id=1
          while [ $Id -le $Maxindid ]
          do
              echo "use "$DATABASE"\ngo\nselect '|ok|'+convert(varchar,indid)+'|'+convert(varchar,keycnt)+'|'+convert(varchar,(status2 & 512))+'|'+convert(varchar,(status & 2))+'|'+name+'|' from sysindexes where id=object_id('"$OWNER"."$OBJECT"') and indid ="$Id" and status2 & 2048 !=2048 \ngo"|isql -S $server -U $username -P $password >; .result
              Indid=`cat .result|grep "|ok|"|awk -F"|" '{print $3}'`
              Indid=`echo $Indid`
              Keycnt=`cat .result|grep "|ok|"|awk -F"|" '{print $4}'`
              name=`cat .result|grep "|ok|"|awk -F"|" '{print $7}'`
              #
              if [ -z "$Indid" ];then
                  Id=`expr $Id + 1`
                  continue
              fi
              #
              if [ $Indid -eq 1 ];then
                  ClustInfo="CLUSTERED"
              else
                  Status2=`cat .result|grep "|ok|"|awk -F"|" '{print $5}'`
                  if [ $Status2 -eq 512 ];then
                      ClustInfo="CLUSTERED"
                  else
                      ClustInfo="NONCLUSTERED"
                  fi
              fi
              #
              Status2=`cat .result|grep "|ok|"|awk -F"|" '{print $6}'`
              if [ $Status2 -eq 2 ];then
                  UniqueInfo="UNIQUE "
              else
                  UniqueInfo=""
              fi
              #
              IndexCols=`GetIndCol $Indid $Keycnt`
              #
              printf "  CREATE %s%s INDEX %s \n      ON %s.%s(%s) \ngo\n" "$UniqueInfo" "$ClustInfo" "$name" "$OWNER" "$OBJECT" "$IndexCols"
              Id=`expr $Id + 1`
          done
      fi
      ;;
  V|P|TR)
      #echo "use "$DATABASE"\ngo\nselect text from syscomments where id=object_id('"$OWNER"."$OBJECT"')\ngo"|isql -S $server -U $username -P $password -b>; .result
      #cat .result|sed 's/ *$//g'|sed 's/^         //g'|grep -v "^$"|awk '{print $0}'
      defncopy -S $server -U $username -P $password out .result $DATABASE $OWNER"."$OBJECT
      cat .result
      ;;
  *)
      echo $USAGE
      echo "WARNING:Only Supply system table,user table,view,proedure,trigger's ddl."$TYPE"."
      ;;
esac
rm -f .result
exit 0

Eisen

不错。
感谢。

大头虎

脚本中两个笑脸分别是
：P

8）

trytodoit

虎哥：您写的那个脚本好长哟（可见您之历害，佩服！佩服!），叫我等菜鸟看了是一头雾水，我不但sybase菜，unix更菜，能否给些帮助，给点什么"unix脚本扫肓篇"之类的如何?先谢了!

trytodoit

对不起，虎哥，此问题应在unix交流区找到答案，幸好你不在线

Blackrose

呵呵 新人辈出 江湖不知又要掀起怎么的血雨腥风...


不过我还是建议你使用 sybase 提供的脚本导出工具。
另外补充一点，sybase central一样从windows上可以连接到Unix的sybase ASE上进行脚本导出。

大头虎

[quote="Blackrose"]呵呵 新人辈出 江湖不知又要掀起怎么的血雨腥风...

quote]

哈哈，夸张了吧...^_^