近日需要用到查询Oracle数据表字段的信息的脚本,这里把它们整理一下。
SELECT B.TABLE_NAME AS "表名",
C.COMMENTS AS "表说明",
B.COLUMN_ID AS "字段序号",
B.COLUMN_NAME AS "字段名",
B.DATA_TYPE AS "字段数据类型",
B.DATA_LENGTH AS "数据长度",
B.DATA_PRECISION AS "整数位",
B.DATA_SCALE AS "小数位",
A.COMMENTS AS "字段说明"
FROM ALL_COL_COMMENTS A, ALL_TAB_COLUMNS B, ALL_TAB_COMMENTS C
WHERE A.TABLE_NAME IN (SELECT U.TABLE_NAME FROM USER_ALL_TABLES U)
AND A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND C.TABLE_NAME = A.TABLE_NAME
AND C.OWNER = A.OWNER
AND B.TABLE_NAME like 'PERFO_%'
ORDER BY B.TABLE_NAME,B.COLUMN_ID;
下面是MSSQL 的表字段信息查询脚本:
SELECT d.name AS 表名, ISNULL(f.value, '') AS 表说明, a.colorder AS 字段序号, a.name AS 字段名,
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = 'PK' AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = a.id AND colid = a.colid))) THEN '√' ELSE '' END AS 主键, b.name AS 类型,
a.length AS 占用字节数, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度, ISNULL(COLUMNPROPERTY(a.id,
a.name, 'Scale'), 0) AS 小数位数, CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.value, '') AS 字段说明
FROM sys.syscolumns AS a LEFT OUTER JOIN
sys.systypes AS b ON a.xtype = b.xusertype INNER JOIN
sys.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT OUTER JOIN
sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN
sys.extended_properties AS f ON d.id = f.major_id AND f.minor_id = 0