Oracle、mysql、dm、pgsql、sqlsever获取对应的字段类型、字段长度、主键、注释。

数据库类型：ORACLE、DM

1.查询表注释并拼接成sql：

select * from all_tables where TABLE_NAME = '查询的表名';

2.查询字段注释并拼接成sql

select 'comment on column '||table_name||'.'||column_name||' is '||''''||comments||''';' from all_col_comments where table_name='查询的表名';

 3.查询表名、字段名称、字段类型、字段长度、对应注释，ALL_COL_COMMENTS和ALL_TAB_COLUMNS，

--oracle/dm
SELECT
  atc.TABLE_NAME, --表名
  atc.COLUMN_NAME, --列名
  atc.DATA_TYPE, --字段类型
  atc.DATA_LENGTH,--字段长度
  atc.DATA_PRECISION, 
  atc.DATA_SCALE,
  acc.COMMENTS--注释
FROM
  ALL_TAB_COLUMNS atc
  LEFT JOIN ALL_COL_COMMENTS acc ON (atc.TABLE_NAME = acc.TABLE_NAME AND atc.OWNER = acc.OWNER AND atc.COLUMN_NAME = acc.COLUMN_NAME)
WHERE
  1 = 1
  AND atc.TABLE_NAME = '指定查询的表名' 
  AND atc.OWNER = '指定查询的数据库的用户'--oracle用这个
  AND acc.SCHEMA_NAME = '指定查询的数据库的用户'--DM用这个

 注意：oracle和dm所使用的的属主（数据库名称）关键字不一样，oracle用“OWNER”，dm用“SCHEMA_NAME ”

获取主键

select a.constraint_name,  a.column_name 
from all_cons_columns a, all_constraints b 
where a.constraint_name = b.constraint_name 
  and a.OWNER = b.OWNER
  and b.constraint_type = 'P' 
  and a.table_name = '查询的表名';

数据库类型：MYSQL、MARIADB

查询建表语句：

show create table 查询的表名称;

 通过SHOW语句查询字段名称、字段类型、字段长度、是否可为空、是否主键、对应注释。

SHOW full COLUMNS FROM `查询的表名`;

查询结果如下图：

数据库类型：SQLSEVER、MSSQL

查询字段名称、字段类型、字段长度、是否可为空、对应注释。

SELECT 
  表名 = case when a.colorder = 1 then d.name else '' end, 
  表说明 = case when a.colorder = 1 then isnull(f.value, '') else '' end, 
  字段序号 = a.colorder, 
  字段名 = a.name, 
  标识 = case when COLUMNPROPERTY(a.id, a.name, 'IsIdentity')= 1 then '√' else '' end, 
  主键 = case when exists(
    SELECT 
      1 
    FROM 
      sysobjects 
    where 
      xtype = 'PK' 
      and parent_obj = a.id 
      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, 
  类型 = b.name, 
  占用字节数 = a.length, 
  长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'), 
  小数位数 = isnull(
    COLUMNPROPERTY(a.id, a.name, 'Scale'), 
    0
  ), 
  允许空 = case when a.isnullable = 1 then '√' else '' end, 
  默认值 = isnull(e.text, ''), 
  字段说明 = isnull(g.[value], '') 
FROM 
  syscolumns a 
  left join systypes b on a.xusertype = b.xusertype 
  inner join sysobjects d on a.id = d.id 
  and d.xtype = 'U' 
  and d.name<>'dtproperties' 
  left join syscomments e on a.cdefault = e.id 
  left join sys.extended_properties g on a.id = G.major_id 
  and a.colid = g.minor_id 
  left join sys.extended_properties f on d.id = f.major_id 
  and f.minor_id = 0 
where 
  d.name = '数据库表名' --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
order by 
  a.id, 
  a.colorder

 查询结果如下图：

获取主键sql：

SELECT
	object_name(c.object_id) AS table_name,
	c.name AS column_name,
	i.name AS primary_key_name
FROM
	sys.index_columns ic
INNER JOIN sys.columns c ON
	ic.object_id = c.object_id
	AND ic.column_id = c.column_id
INNER JOIN sys.indexes i ON
	ic.object_id = i.object_id
	AND ic.index_id = i.index_id
WHERE
	i.is_primary_key = 1
    AND object_name(c.object_id) = '查询的表名'

 数据库类型：POSTGRESQL

select
	c.relname 表名,
	a.attname as columnName,
	d.description as comments,
	concat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '\(.*\)')) as fieldType,
	a.attnotnull as notnull
from
	pg_class c,
	pg_attribute a,
	pg_type t,
	pg_description d
where
	a.attnum>0
	and 
  a.attrelid = c.oid
	and 
  a.atttypid = t.oid
	and 
  d.objoid = a.attrelid
	and 
  d.objsubid = a.attnum
	and 
  c.relname = '查询的表名称'

可获取到对应的表名称、字段名称、注释、字段类型、是否可为空

获取主键：可获取到主键字段名称

select
	string_agg (distinct t3.attname,
	',') as primaryKeyColumn
from
	pg_constraint t1
inner join pg_class t2 on
	t1.conrelid = t2.oid
inner join pg_attribute t3 on
	t3.attrelid = t2.oid
	and array_position (t1.conkey,
	t3.attnum) is not null
where
	t1.contype = 'p'
	and length (t3.attname) > 0
	and t2.oid = '查询的表名称'::regclass