SQL查询表字段信息详细图文教程,
目录
- 一、MySQL
- (一)查询所有表名
- (二)查询表下所有字段
- 二、Oracle
- (一)查询表下所有字段
- (二)查询库下表字段信息
- (三)查询用户下表的外建等信息
- (四)oracle查询用户下表外建信息
- 三、Sql Server
- 总结
一、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查询表字段信息内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友!
您可能感兴趣的文章:- 关于 MySQL 嵌套子查询中无法关联主表字段问题的解决方法
- Mysql查询表字段结构注释的方式
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。