欢迎投稿

今日深度:

SQL查询表字段信息详细图文教程,

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查询表字段结构注释的方式

      www.htsjk.Com true http://www.htsjk.com/Mysql/47807.html NewsArticle SQL查询表字段信息详细图文教程, 目录 一、MySQL (一)查询所有表名 (二)查询表下所有字段 二、Oracle (一)查询表下所有字段 (二)查询库下表字段信息 (三)查询用户下表的外...
      评论暂时关闭