从Sybase数据库，我如何获取表描述（字段名称和类型）？

I have access to command line isql and I like to get Meta-Data of all the tables of a given database, possibly in a formatted file. How I can achieve that?

我可以访问命令行isql，我喜欢获取给定数据库的所有表的元数据，可能是格式化文件。我怎么能做到这一点？

Thanks.

谢谢。

11 个解决方案

#1

Check sysobjects and syscolumns tables.

检查sysobjects和syscolumns表。

Here is a diagram of Sybase system tables.

这是Sybase系统表的图表。

List of all user tables:

所有用户表的列表：

SELECT * FROM sysobjects WHERE type = 'U'

You can change 'U' to other objects:

您可以将“U”更改为其他对象：

C – computed column
C - 计算列
D – default
D - 默认
F – SQLJ function
F - SQLJ函数
L – log
L - 日志
N – partition condition
N - 分区条件
P – Transact-SQL or SQLJ procedure
P - Transact-SQL或SQLJ过程
PR – prepare objects (created by Dynamic SQL)
PR - 准备对象（由Dynamic SQL创建）
R – rule
R - 规则
RI – referential constraint
RI - 参考约束
S – system table
S - 系统表
TR – trigger
TR - 触发器
U – user table
U - 用户表
V – view
V - 视图
XP – extended stored procedure
XP - 扩展存储过程

List of columns in a table:

表中的列列表：

SELECT sc.* 
FROM syscolumns sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE so.name = 'my_table_name'

#2

sp_help is what you're looking for.

sp_help是你正在寻找的。

From Sybase online documentation on the sp_help system procedure:

有关sp_help系统过程的Sybase联机文档：

Description

描述

Reports information about a database object (any object listed in sysobjects) and about system or user-defined datatypes, as well as computed columns and function-based indexes. Column displays optimistic_index_lock.

报告有关数据库对象（sysobjects中列出的任何对象）以及系统或用户定义的数据类型以及计算列和基于函数的索引的信息。列显示optimistic_index_lock。

Syntax

句法

sp_help [objname]

sp_help [objname]

[...]

[...]

Here is the (partial) output for the publishers table (pasted from Using sp_help on database objects):

以下是publishers表的（部分）输出（粘贴自在数据库对象上使用sp_help）：

Name               Owner        Object_type     Create_date 
----------------   -----------  -------------   ------------------------------
publishers         dbo          user table      Nov 9 2004 9:57AM

(1 row affected)
Column_name Type     Length   Prec  Scale   Nulls   Default_name   Rule_name
----------- -------  ------   ----- ------- ------- -------------- ---------- 
pub_id      char          4    NULL  NULL        0  NULL           pub_idrule
pub_name    varchar      40    NULL  NULL        1  NULL           NULL
city        varchar      20    NULL  NULL        1  NULL           NULL
state       char          2    NULL  NULL        1  NULL           NULL
Access_Rule_name    Computed_Column_object     Identity
------------------- -------------------------  ------------
NULL                NULL                                  0
NULL                NULL                                  0
NULL                NULL                                  0
NULL                NULL                                  0

Still quoting Using sp_help on database objects:

仍引用在数据库对象上使用sp_help：

If you execute sp_help without supplying an object name, the resulting report shows each object in sysobjects, along with its name, owner, and object type. Also shown is each user-defined datatype in systypes and its name, storage type, length, whether null values are allowed, and any defaults or rules bound to it. The report also notes if any primary or foreign key columns have been defined for a table or view.

如果在不提供对象名称的情况下执行sp_help，则生成的报告将显示sysobjects中的每个对象及其名称，所有者和对象类型。还显示了systypes中每个用户定义的数据类型及其名称，存储类型，长度，是否允许空值以及绑定到它的任何默认值或规则。该报告还指出是否已为表或视图定义了任何主键或外键列。

#3

Sybase IQ:

Sybase IQ：

describe table_name;

#4

     SELECT
DB_NAME() TABLE_CATALOG,
NULL TABLE_SCHEMA,
so.name TABLE_NAME,
sc.name COLUMN_NAME,
sc.colid ORDINAL_POSITION,
NULL COLUMN_DEFAULT,
CASE WHEN st.allownulls=1 THEN 'YES'
 ELSE 'NO'
END IS_NULLABLE,
st.name DATA_TYPE,
CASE WHEN st.name like '%char%' THEN st.length
END CHARACTER_MAXIMUM_LENGTH,
CASE WHEN st.name like '%char%' THEN st.length
END*2 CHARACTER_OCTET_LENGTH,
CASE WHEN st.name in ('numeric','int') THEN st.length
END NUMERIC_MAXIMUM_LENGTH,
CASE WHEN st.name in ('numeric','int') THEN st.prec
END NUMERIC_PRECISION,
NULL NUMERIC_PRECISION_RADIX,
CASE WHEN st.name in ('numeric','int') THEN st.scale
END NUMERIC_SCALE,
CASE WHEN st.name in ('datetime') THEN st.prec
END DATETIME_PRECISION,
NULL CHARACTER_SET_CATALOG,
NULL CHARACTER_SET_SCHEMA,
NULL COLLATION_CATALOG,
NULL COLLATION_SCHEMA,
NULL DOMAIN_CATALOG,
NULL DOMAIN_SCHEMA,
NULL DOMAIN_NAME
FROM 
sysobjects so
INNER JOIN 
syscolumns sc
ON sc.id = so.id
inner join systypes st on st.usertype = sc.usertype 
WHERE so.name = 'TableName'

#5

sp_tables will also work in isql. It gives you the list of tables in the current database.

sp_tables也可以在isql中使用。它为您提供当前数据库中的表列表。

#6

You can search for column in all tables in database using:

您可以使用以下方法在数据库的所有表中搜索列：

SELECT so.name 
FROM sysobjects so
INNER JOIN syscolumns sc ON so.id = sc.id 
WHERE sc.name = 'YOUR_COLUMN_NAME'

#7

When finding user table, in case if want the table owner name also, you can use the following:

查找用户表时，如果还想要表所有者名称，可以使用以下命令：

select su.name + '.' + so.name
from   sysobjects so,
       sysusers   su
where  so.type = 'U' and
       so.uid  = su.uid
order  by su.name,
          so.name

#8

If you want to use a command line program, but are not restricted to using SQL, you can use SchemaCrawler. SchemaCrawler is open source, and can produce files in plain text, CSV, or (X)HTML formats.

如果要使用命令行程序，但不限于使用SQL，则可以使用SchemaCrawler。 SchemaCrawler是开源的，可以生成纯文本，CSV或（X）HTML格式的文件。

#9

Here a different approach to get meta data. This very helpful SQL command returns you the table / view definition as text:

这里有一种获取元数据的不同方法。这个非常有用的SQL命令将表/视图定义作为文本返回：

SELECT text FROM syscomments WHERE id = OBJECT_ID('MySchema.MyTable') ORDER BY number, colid2, colid

SELECT text FROM syscomments WHERE id = OBJECT_ID（'MySchema.MyTable'）ORDER BY number，colid2，colid

Enjoy Patrick

享受Patrick

#10

In the Sybase version I use, the following gives list of columns for selected table

在我使用的Sybase版本中，以下列出了所选表的列列表

select *
FROM sys.syscolumns sc
where tname = 'YOUR_TABLE_NAME'
--and creator='YOUR_USER_NAME' --if you want to further restrict tables
--according to the user name that created it

#11

-1

If Sybase is SQL-92 compliant then this information is stored within the INFORMATION_SCHEMA tables.

如果Sybase符合SQL-92，则此信息存储在INFORMATION_SCHEMA表中。

So the following will give you a list of tables and views in any SQL-92 compliant database

因此，以下内容将为您提供任何符合SQL-92的数据库中的表和视图列表

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

#1