获取ORACLE和SQL SERVER表或视图结构（字段名、字段长度、精确位数、是否可空、字段描述、字段默认值、主键字段）

 获取ORACLE 和 SQL SERVER表或视图结构（字段名、字段长度、精确位数、是否可空、字段描述、字段默认值、主键字段）

-- ORACLE获取表结构
SELECT UTCS.COLUMN_NAME AS COLNAME,
       CASE
         WHEN CHAR_LENGTH > 0 THEN
          UTCS.DATA_TYPE || '(' || UTCS.CHAR_LENGTH || ')'
         ELSE
          UTCS.DATA_TYPE
       END AS COLTYPENAME,
       NVL(UTCS.DATA_PRECISION, '0') AS COLPRECISION,
       NVL(UTCS.DATA_SCALE, '0') AS COLSCALE,
       CASE (UTCS.NULLABLE)
         WHEN 'N' THEN
          '0'
         ELSE
          '1'
       END AS ISNULLABLE,
       UTCS.DATA_DEFAULT AS COLDEFAULT,
       UCCS.COMMENTS AS COLDESC,
       CASE
         WHEN COLS.COLUMN_NAME IS NOT NULL THEN
          '1'
         ELSE
          '0'
       END AS KEYCOL
  FROM USER_TAB_COLUMNS UTCS
  LEFT JOIN USER_COL_COMMENTS UCCS
    ON UTCS.TABLE_NAME = UCCS.TABLE_NAME
   AND UTCS.COLUMN_NAME = UCCS.COLUMN_NAME
  LEFT JOIN USER_CONSTRAINTS UCS
    ON UCS.TABLE_NAME = UTCS.TABLE_NAME
   AND UCS.CONSTRAINT_TYPE = 'P'
  LEFT JOIN USER_CONS_COLUMNS COLS
    ON COLS.TABLE_NAME = UTCS.TABLE_NAME
   AND COLS.COLUMN_NAME = UTCS.COLUMN_NAME
   AND UCS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
 WHERE UTCS.TABLE_NAME = '{tableName.ToUpper()}'





-- SQL SERVER获取表结构
SELECT SYSCOLUMNS.NAME                                                    COLNAME,
       SYSTYPES.NAME                                                      COLTYPENAME,
       COLUMNPROPERTY(SYSCOLUMNS.ID, SYSCOLUMNS.NAME, 'PRECISION')        COLPRECISION,
       ISNULL(COLUMNPROPERTY(SYSCOLUMNS.ID, SYSCOLUMNS.NAME, 'SCALE'), 0) COLSCALE,
       SYSCOLUMNS.ISNULLABLE                                              ISNULLABLE,
       CASE
         WHEN SYSCOMMENTS.TEXT IS NULL THEN ''
         ELSE SUBSTRING(SYSCOMMENTS.TEXT, 2, LEN(SYSCOMMENTS.TEXT) - 2)
       END                                                                COLDEFAULT,
       ISNULL(SYS.EXTENDED_PROPERTIES.[VALUE], '')                        COLDESC,
       CASE
         WHEN EXISTS (SELECT 1
                      FROM   SYSOBJECTS
                      WHERE  XTYPE = 'PK'
                             AND PARENT_OBJ = SYSCOLUMNS.ID
                             AND NAME IN (SELECT NAME
                                          FROM   SYSINDEXES
                                          WHERE  INDID IN (SELECT INDID
                                                           FROM   SYSINDEXKEYS
                                                           WHERE  ID = SYSCOLUMNS.ID
                                                                  AND COLID = SYSCOLUMNS.COLID))) THEN '1'
         ELSE '0'
       END                                                                KEYCOL
FROM   SYSCOLUMNS
       LEFT JOIN SYSTYPES
              ON SYSCOLUMNS.XUSERTYPE = SYSTYPES.XUSERTYPE
       LEFT JOIN SYSOBJECTS
              ON SYSCOLUMNS.ID = SYSOBJECTS.ID
                 AND ( SYSOBJECTS.XTYPE = 'U'
                        OR SYSOBJECTS.XTYPE = 'V' )
       LEFT JOIN SYSCOMMENTS
              ON SYSCOMMENTS.ID = SYSCOLUMNS.CDEFAULT
       LEFT JOIN SYS.EXTENDED_PROPERTIES
              ON SYSCOLUMNS.ID = SYS.EXTENDED_PROPERTIES.MAJOR_ID
                 AND SYSCOLUMNS.COLID = SYS.EXTENDED_PROPERTIES.MINOR_ID
WHERE  SYSOBJECTS.NAME = '{tableName}'