SQL 从 Sybase 数据库中，如何获取表描述（字段名称和类型）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1429898/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
From a Sybase Database, how I can get table description ( field names and types)?
提问by aartist
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.
谢谢。
回答by Lukasz Lysik
Check sysobjectsand syscolumnstables.
检查sysobjects和syscolumns表。
Hereis 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
- D – default
- F – SQLJ function
- L – log
- N – partition condition
- P – Transact-SQL or SQLJ procedure
- PR – prepare objects (created by Dynamic SQL)
- R – rule
- RI – referential constraint
- S – system table
- TR – trigger
- U – user table
- V – view
- XP – extended stored procedure
- C – 计算列
- D – 默认
- F – SQLJ 函数
- L – 日志
- N——分区条件
- P – Transact-SQL 或 SQLJ 过程
- PR – 准备对象（由动态 SQL 创建）
- R – 规则
- RI——引用约束
- S——系统表
- TR——触发器
- U – 用户表
- V – 视图
- 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'
回答by Pascal Thivent
sp_helpis what you're looking for.
sp_help就是你要找的。
From Sybase online documentation on the sp_helpsystem 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.
Syntax
sp_help [objname][...]
描述
报告有关数据库对象（sysobjects 中列出的任何对象）和有关系统或用户定义的数据类型以及计算列和基于函数的索引的信息。列显示optimistic_index_lock。
句法
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 中的每个用户定义的数据类型及其名称、存储类型、长度、是否允许空值以及绑定到它的任何默认值或规则。该报告还会指出是否为表或视图定义了任何主键或外键列。
回答by Sergey Zinyuk
Sybase IQ:
Sybase IQ：
describe table_name;
回答by Kishore
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'
回答by MDn
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'
回答by shanmugs
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
回答by Chris Morgan
sp_tableswill also work in isql. It gives you the list of tables in the current database.
sp_tables也将在 isql 中工作。它为您提供当前数据库中的表列表。
回答by Patrick Wolf
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
享受帕特里克
回答by Sualeh Fatehi
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 格式的文件。
回答by hui chen
For Sybase ASE, sp_columns table_name will return all the table metadata you are looking for.
对于 Sybase ASE， sp_columns table_name 将返回您要查找的所有表元数据。
