如何获得Db2的表结构？

[conis]

我想获取数据库的表结构信息，分别要取得表名，注释，是否为空，是否为主健等信息，示例(SQL Server)

/*
Author:Conis
CreateDate:16:21 2007-08-09
Description:
获取指定表的所有字段属性
Remark:
　1.如果你不精通SQL代码，请不要随便修改，以免程序不能正常运行。
` 2.字段名称不能修改，否则会导致程序错误
3.如果你一定要修改，建议在修改之前做好备份
=======================================================================
| CopyRight(C)Conis YI |
| URL:http://www.conis.cn |
| E-Mail:[e-mail] |
=======================================================================
*/

DECLARE @Version VARCHAR(100)
DECLARE @SysTable VARCHAR(50)
DECLARE @sql NVARCHAR(2000)
DECLARE @SmallID VARCHAR(10)
DECLARE @MajorID VARCHAR(10)

SET @Version = @@VERSION
IF CHARINDEX('9.00', @Version) = 0 --2005
BEGIN
SET @SysTable = 'sysproperties'
SET @SmallID = 'smallid'
SET @MajorID = 'id'
END
ELSE --2000
BEGIN
SET @SysTable = 'sys.extended_properties'
SET @SmallID = 'minor_id'
SET @MajorID = 'major_id'
END

SET @sql =
'SELECT
col.name AS ''FieldName'',
(CASE WHEN (SELECT COUNT(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = col.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = col.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = col.id) AND (name = col.name))))))) AND
(xtype = ''PK''))>0 THEN 1 ELSE 0 END) AS ''IsKey'',
typ.name AS ''Type'',
col.length AS ''Length'',
ext.[value] AS ''Description'',
col.isnullable AS ''AllowNull'',
com.text AS ''DefaultValue'',
col.colstat AS ''IsOutPut'',
obj.Name AS ''TableName''

FROM syscolumns col
LEFT JOIN systypes typ on col.xtype = typ.xusertype
INNER JOIN sysobjects obj on col.id = obj.id
LEFT JOIN syscomments com on col.cdefault = com.id
LEFT JOIN [PROPERTIES] ext on col.id = ext.[MAJORID] AND col.colid = ext.[SMALLID]
WHERE
obj.name in ({0}) AND (obj.xtype = ''U'' OR obj.xtype = ''V'')
ORDER BY obj.name'

SET @sql = REPLACE(@sql, '[PROPERTIES]', @SysTable)
SET @sql = REPLACE(@sql, '[SMALLID]', @SmallID)
SET @sql = REPLACE(@sql, '[MAJORID]', @MajorID)
EXEC sp_executesql @sql

/*
Author:Conis
CreateDate:16:50 2007-08-09
Description:
获取所有表的属性
Remark:
　1.如果你不精通SQL代码，请不要随便修改，以免程序不能正常运行。
` 2.字段名称不能修改，否则会导致程序错误
3.如果你一定要修改，建议在修改之前做好备份
=======================================================================
| CopyRight(C)Conis YI |
| URL:http://www.conis.cn |
| E-Mail:[e-mail] |
=======================================================================
*/
DECLARE @Version VARCHAR(100)
DECLARE @SysTable VARCHAR(50)
DECLARE @sql NVARCHAR(1000)
DECLARE @SmallID VARCHAR(10)

SET @Version = @@VERSION
IF CHARINDEX('9.00', @Version) = 0 --2005
BEGIN
SET @SysTable = 'sysproperties'
SET @SmallID = 'smallid'
END
ELSE --2000
BEGIN
SET @SysTable = 'sys.extended_properties'
SET @SmallID = 'minor_id'
END

SET @sql = '
SELECT obj.name AS ''Name'',
CASE --如果是表，则查询表的备注信息。
WHEN obj.xtype = ''U'' THEN
(SELECT TOP 1 ext.value FROM [PROPERTIES] ext WHERE
ext.[SMALLID] = obj.id and ext.[SMALLID] = 0
)
ELSE ''''
END AS ''Description'',
CASE obj.xtype
WHEN ''U'' THEN ''Table''
WHEN ''P'' THEN ''Procedure''
WHEN ''V'' THEN ''View''
END AS ''Type''
FROM sysobjects obj
WHERE (obj.xtype = ''U'' OR obj.xtype = ''V'')
ORDER BY obj.xtype'
SET @sql = REPLACE(@sql, '[PROPERTIES]', @SysTable)
SET @sql = REPLACE(@sql, '[SMALLID]', @SmallID)
EXEC sp_executesql @sql

[yangxiao_jiang]

select * from SYSIBM.SYSTABLES

[littleboys]

db2look -d 数据库名 -i 用户名-w 密码 -e -o ./文件名.txt

[王帆]

楼上的可以，就是生成的文件格式太乱了，不好看呀。而且除了LZ要看的以外东西太多，还有更好的办法吗？

-------------------------------------------
结果摘录：
TableSpaceName & NTables
& TableName & Creator & CARD & NPAGES & FPAGES & OVFLOW & ACTBLK & IndexTS & LongTS \\ \hline \hline
SYSCATSPACE & 105 &
SYSTABLES & SYSIBM & 0 & 0 & 0 & 0 & 0 & - & -\\ \hline
SYSCATSPACE & 105 &
SYSTABLESPACES & SYSIBM & 0 & 0 & 0 & 0 & 0 & - & -\\ \hline
USERSPACE1 & 18 &
GU & EFAN & 0 & 0 & 0 & 0 & 0 & - & -\\ \hline
USERSPACE1 & 18 &
GU\_BAK & EFAN & 0 & 0 & 0 & 0 & 0 & - & -\\ \hline
USERSPACE1 & 18 &
RING\_INFOS & EFAN & 0 & 0 & 0 & 0 & 0 & - & -\\ \hline

......

%********************************************
\newpage


\subsection*{{\bf T} $\bullet$ Columns}
\begin{tabular}{|l|l|l|l|l|l|l|l|l|l|l|l|} \hline
\multicolumn{12}{|p{7in}|}{DB2 Catalog Statistics - Columns of table {\sf T}
- on database {\em TESTDB} created by {\em EFAN } }\\ \hline
Name & Type & No & Len & AvgLen & Null & Card & NumNULLS & SUB\_COUNT & SUB\_DEL\_L &NFrq & NQuan\\ \hline \hline
UID & BIGINT & 0 & 8 & 8 & N & -1 & 0 & 0 & -1 & 8 & -1 \\ \hline
GID & BIGINT & 1 & 8 & -1 & N & -1 & -1 & -1 & -1 & -1 & -1 \\ \hline
GUDATE & DATE & 2 & 4 & -1 & N & -1 & -1 & -1 & -1 & -1 & -1 \\ \hline
\end{tabular}

\vspace{0.5cm}

\vspace{0.5cm}

\noindent\begin{tabular}{|l|l|l|} \hline
\multicolumn{3}{|p{6in}|}{DB2 Catalog Statistics - High2Key and Low2Key of Columns of table {\sf T}} \\ \hline
Name & Low2Key & High2Key \\ \hline \hline
UID & & \\ \hline
GID & & \\ \hline
GUDATE & & \\ \hline
\end{tabular}

\newpage

[王帆]

其实LZ可以查看数据库所有表：
db2 connect to dbname;
db2 list tables;
查看所有表结构（不含注释）：
db2 -x "select 'describe table '||char(tabname)||';' from syscat.tables" >d:/table.sql;
db2 -vtf e:/table.sql >d:/tabledetail.txt;
所有表结构都在tabledetail.txt中了，一个表的例子：

describe table USERS


列 类型 类型
名称 模式 名称 长度 小数位 NUL
L
------------------------------ --------- ------------------ -------- ----- -----
-
UID SYSIBM BIGINT 8 0 否

UNAME SYSIBM VARCHAR 10 0 是

UPAY SYSIBM INTEGER 4 0 否

DATE_PAY SYSIBM DATE 4 0 是


4 条记录已选择。

[Mr_Bean]

describe table tabname

[huoyin]

db2 describe table [tablename] > table_struct.txt

[conis]

汗，我不要导出，我要查询出来成一个记录集，不是用来看的。

[yangxiao_jiang]

select * from SYSIBM.SYSTABLES
这样查询不出结果集？？

[Mr_Bean]

这样啊，拿你直接看syscat.columns或者sysibm.syscolumns系统表就可以了里面东西应该是全面地

[meiZiNick]

都是很好的建议! 值得学习