Oracle表结构查询语句



SELECT a.column_id

      ,''   AS NAME

      ,a.column_name

      ,a.data_type

      ,''

      ,CASE

            WHENa.data_type = 'NUMBER' THEN CASE

                                                  WHENa.DATA_SCALE = 0 OR a.DATA_SCALE IS NULL THEN to_char(a.DATA_PRECISION)

                                                 ELSE to_char(a.DATA_PRECISION) || ',' || to_char(a.DATA_SCALE)

                                            END

           WHEN a.data_type = 'DATE' THEN null

           ELSE to_char(a.data_length)

       END  AS LEN

      ,''

      ,a.data_default

      ,CASE a.nullable

           WHEN 'N' THEN 'ON'

           ELSE ''

       END  ASnullable

      ,CASE

            WHENa.column_name IN (SELECT b.column_name

                                  FROM   user_constraints c

                                         LEFT JOIN user_cons_columns b

                                              ON  c.constraint_name = b.constraint_name

                                  WHERE  c.Table_Name = a.table_name

                                         AND constraint_type = 'P') THEN '○'

           ELSE ''

       END AS KEY

FROM   user_tab_columns a

WHERE  a.Table_Name = @TableName

ORDER BY

       column_id