可以查询Oracle和MSSQL数据库的表字段信息的SQL脚本

近日需要用到查询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