我要评论一、mysql
（一）查询所有表名
查询指定数据库下表名
select table_name,table_comment from information_schema.tables where table_schema='grandly_forum_db'
（二）查询表下所有字段
查询指定数据库下表的字段信息
select a.table_schema, a.table_name, b.table_comment 表说明, a.column_name 字段名, a.column_comment 字段说明, a.column_type 字段类型, a.column_key 约束 from information_schema.columns a join information_schema.tables b on a.table_name = b.table_name where a.table_schema = '数据库名' and a.table_name = '表名';
二、oracle
（一）查询表下所有字段
select
distinct
c.owner 用户,
c.table_name 英文表名 ,
t.comments 中文表名称,
cc.column_name 字段名,
cc.comments 字段注释 ,
c.data_type || '(' || c.data_length || ')' 字段类型及长度
from
dba_tab_columns c
join dba_col_comments cc on c.table_name = cc.table_name
and c.column_name = cc.column_name
join dba_tab_comments t on c.table_name = t.table_name
where
c.owner = 'yzzx'
-- and t.comments like '%船舶信息%'
and c.table_name = 'shx_shipcert'
order by
cc.column_name
（二）查询库下表字段信息
with t1 as( select t.owner owner_name, t.table_name, t.column_name, f.comments, t.data_type, t.data_length from all_tab_columns t join all_col_comments f on t.table_name = f.table_name and t.column_name = f.column_name order by t.table_name, t.column_name ), t2 as ( select col.column_name pk,col.table_name from user_constraints con,user_cons_columns col where con.constraint_name=col.constraint_name and con.constraint_type='p' ) select distinct t1.owner_name 用户名, t1.table_name 表名, t2.pk 主键, t1.column_name 字段名, t1.comments 字段注释, t1.data_type 数据类型, t1.data_length 长度 from t1 join t2 on t1.table_name = t2.table_name where t1.owner_name = 'zhzfhzk' and t1.comments is not null order by t1.table_name,column_name
（三）查询用户下表的外建等信息
select distinct c.owner 用户名, constraint_type 键类型, c.table_name 子表名, c.constraint_name 子表键名称, d.table_name 父表名称, c.r_constraint_name 父表键名称, d.column_name 字段名称 from all_constraints c join all_cons_columns d on c.constraint_type = 'r' and c.r_constraint_name = d.constraint_name where c.owner = 'scott' ;
（四）oracle查询用户下表外建信息
select
t1.table_name as table_name,
t2.table_name as f_table_name,
t1.column_name,
t2.column_name as f_column_name
from
all_constraints cons
join all_cons_columns col1 on cons.owner = col1.owner and cons.constraint_name = col1.constraint_name
join all_tab_columns t1 on cons.owner = t1.owner and col1.table_name = t1.table_name and col1.column_name = t1.column_name
join all_cons_columns col2 on cons.owner = col2.owner and cons.r_owner = col2.owner and cons.r_constraint_name = col2.constraint_name
join all_tab_columns t2 on cons.r_owner = t2.owner and col2.table_name = t2.table_name and col2.column_name = t2.column_name
where
cons.constraint_type = 'r' and t1.owner = 'wanglin'
order by t1.table_name;
三、sql server
查询数据库(databases)名称:
select name from master.dbo.sysdatabases where status <> 512
查询数据表(tables)名称:
select name from dbo.sysobjects where objectproperty(id,n'isusertable') = 1 and name <> 'dtproperties'
查询带schema 的数据表(tables)名称:
select b.name + '.' + a.name as name from sysobjects a inner join sys.schemas b on a.uid=b.schema_id where objectproperty(id,n'isusertable') = 1 and a.name <> 'dtproperties'
查询数据表(tables)中的字段(columns)名称:
select * from dbo.syscolumns where id=object_id(n'[production].[product]') order by colid
或者
select name from syscolumns where id=object_id('tname') 总结
到此这篇关于sql查询表字段信息的文章就介绍到这了,更多相关sql查询表字段信息内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论