oracle mysql SqlServer 元数据查询sql

oracle mysql SqlServer 元数据查询sql

Sqlserver:
查询所有表名 和表信息
如下：

SELECT 
    表名       = d.name,--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 'Y' else 'N' 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 'Y'else 'N' 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
order by 
    a.id,a.colorder

————————————————
版权声明：本文为CSDN博主「一杯咖啡！」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_37655399/article/details/108452938

mysql:

select 
	'系统名自定义' as Owner,
	'mysql' as source ,
	a.TABLE_SCHEMA as Table_Schema,
	a.TABLE_NAME as TableName,
	b.TABLE_COMMENT as Table_Comment,
	a.COLUMN_NAME as ColumnName ,
	a.COLUMN_COMMENT as Column_Comment,
	a.DATA_TYPE as DataType,
	a.CHARACTER_MAXIMUM_LENGTH  as  LENGTH ,
	a.NUMERIC_PRECISION  as Numeric_Precision,
	a.NUMERIC_SCALE as Scale,
	'' as Column_Id,
	a.COLUMN_KEY as Is_Primary_Key,
	'' as Is_Partition_Key,
	'' as Is_Subpartition_Key,
	a.ORDINAL_POSITION  as Column_Position,
	case when a.IS_NULLABLE = 'YES' then 'Y' else 'N' end as Nullable
	-- a.IS_NULLABLE as Nullable -- (case when a.IS_NULLABLE = 'YES' then '1' else '0') end 
FROM
     information_schema.COLUMNS AS a 
LEFT JOIN
    information_schema.TABLES AS b
ON
    a.TABLE_NAME=b.TABLE_NAME
AND a.TABLE_SCHEMA=b.TABLE_SCHEMA
where b.TABLE_TYPE='BASE TABLE'
and b.table_schema in ('数据库名')

```sql
oracle 最新
select 
 DISTINCT (o.OWNER||o.TABLE_NAME||o.COLUMN_NAME) as disflag,
  CASE WHEN o.OWNER = 'WXNHW' THEN 'sdpw' WHEN o.OWNER = 'BZ_EBIZ' THEN 'sdhx' ELSE '' END as Owner,
  'oracle'  as source,
  o.OWNER   as Table_Schema,
  o.TABLE_NAME        as TableName,
  o.tab_comments          as Table_Comment,
  o.COLUMN_NAME         as ColumnName ,
  o.comments          as Column_Comment,
  o.DATA_TYPE         as DataType,
  o.DATA_LENGTH         as LENGTH ,
  o.DATA_PRECISION        as NUMERIC_PRECISION,
  o.DATA_SCALE          as Scale,
  ''         as Column_Id,
  o.IS_PRIMARY_KEY         as Is_Primary_Key,
  o.IS_PARTITION_KEY              as Is_Partition_Key,
  ''              as Is_Subpartition_Key,
  o.COLUMN_ID              as Column_Position,
  o.NULLABLE          as Nullable 
 
FROM
  (select m.*,n.tab_comments from 
    (select a.OWNER,a.table_name,b.comments as tab_comments from all_tables a ，all_tab_comments b 
      where a.OWNER in ('库名1','库名2')
      and   b.OWNER = a.OWNER 
      and a.table_name = b.table_name
    ) n,
    (select c.*,d.comments ,
    CASE WHEN c.column_name = e.column_name THEN 'Y' ELSE 'N' END AS IS_PRIMARY_KEY,
    CASE WHEN c.column_name = g.column_name THEN 'Y' ELSE 'N' END AS IS_PARTITION_KEY
   from all_tab_columns  c

  left join all_col_comments   d
      on c.OWNER = d.OWNER  and c.TABLE_NAME = d.table_name and c.COLUMN_NAME = d.column_name
  left join dba_cons_columns e
    ON c.OWNER = e.OWNER  and c.table_name = e.TABLE_NAME and c.column_name= e.column_name and e.position is not null
    left join  dba_constraints f
    on e.constraint_name = f.constraint_name and f.constraint_type ='P' and f.owner = e.OWNER and f.table_name = e.TABLE_NAME
  left join  DBA_PART_KEY_COLUMNS g 
    on   g.OBJECT_TYPE ='TABLE' 
      and c.OWNER = g.OWNER  and c.table_name=g.NAME 
    
    where c.owner in ('库名1','库名2')
    ) m
    where n.owner = m.owner and n.table_name = m.table_name) o 
  order by o.owner,o.TABLE_NAME , o.COLUMN_ID

oracle：

```sql
select 
  CASE WHEN o.OWNER = '数据库' THEN '系统名' WHEN o.OWNER = '数据库' THEN '系统名' ELSE '' END as Owner,
  'oracle'  as source,
  o.OWNER   as Table_Schema,
  o.TABLE_NAME        as TableName,
  o.tab_comments          as Table_Comment,
  o.COLUMN_NAME         as ColumnName ,
  o.comments          as Column_Comment,
  o.DATA_TYPE         as DataType,
  o.DATA_LENGTH         as LENGTH ,
  o.DATA_PRECISION        as NUMERIC_PRECISION,
  o.DATA_SCALE          as Scale,
  ''         as Column_Id,
  o.IS_PRIMARY_KEY         as Is_Primary_Key,
  o.IS_PARTITION_KEY              as Is_Partition_Key,
  ''              as Is_Subpartition_Key,
  o.COLUMN_ID              as Column_Position,
  o.NULLABLE          as Nullable 
FROM
  (select m.*,n.tab_comments from 
    (select a.OWNER,a.table_name,b.comments as tab_comments from all_tables a ，all_tab_comments b 
      where a.OWNER in ('数据库名')
      and   b.OWNER = a.OWNER 
      and a.table_name = b.table_name
    ) n,
    (select c.*,d.comments ,
    CASE WHEN c.column_name = TABLE_CONSTRAINT.column_name THEN 'Y' ELSE 'N' END AS IS_PRIMARY_KEY,
    CASE WHEN c.column_name = TABLE_PART_KEY.column_name THEN 'Y' ELSE 'N' END AS IS_PARTITION_KEY
   from (select * from all_tab_columns where owner in ('数据库名')) c
         
      left join (select table_name ,column_name,owner ,comments from all_col_comments )  d
      on c.OWNER = d.OWNER  and c.TABLE_NAME = d.table_name and c.COLUMN_NAME = d.column_name
      left join ( select table_name ,column_name,owner from dba_cons_columns where constraint_name in
      ( select constraint_name from dba_constraints WHERE constraint_type ='P')and owner = dba_cons_columns.OWNER and table_name = dba_cons_columns.TABLE_NAME)) TABLE_CONSTRAINT 
      ON c.OWNER = TABLE_CONSTRAINT.OWNER  and c.table_name = TABLE_CONSTRAINT.TABLE_NAME and c.column_name= TABLE_CONSTRAINT.column_name 
     
      left join(SELECT NAME, COLUMN_NAME ,owner FROM DBA_PART_KEY_COLUMNS where  OBJECT_TYPE ='TABLE')TABLE_PART_KEY
      ON c.OWNER = TABLE_PART_KEY.OWNER  and c.table_name=TABLE_PART_KEY.NAME 
      
    ) m
  where n.owner = m.owner and n.table_name = m.table_name) o 
  order by o.owner,o.TABLE_NAME , o.COLUMN_ID