精华内容
下载资源
问答
  • SELECT table_name,column_name,data_type FROM information_schema."columns" where table_schema='public' and "column_name" not in('create_date','modify_date') and data_type like '%timestamp%'
    SELECT table_name,column_name,data_type
    FROM information_schema."columns" 
    where table_schema='public' 
    and "column_name" not in('create_date','modify_date')  
    and data_type like '%timestamp%'
    
    展开全文
  • 获取:  select table_name from user_tables; //当前用户的  select table_name from all_tables; //所有用户的  select table_name from dba_tables; //包括系统  select table_name from dba_...
    获取表: 

    select table_name from user_tables; //当前用户的表       

    select table_name from all_tables; //所有用户的表   

    select table_name from dba_tables; //包括系统表 

    select table_name from dba_tables where owner='用户名' 

    user_tables: 

    table_name,tablespace_name,last_analyzed等 

    dba_tables: 

    ower,table_name,tablespace_name,last_analyzed等 

    all_tables: 

    ower,table_name,tablespace_name,last_analyzed等 

    all_objects: 

    ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等 

    获取表字段: 

    select * from user_tab_columns where Table_Name='用户表'; 

    select * from all_tab_columns where Table_Name='用户表'; 

    select * from dba_tab_columns where Table_Name='用户表'; 

    user_tab_columns: 

    table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等 

    all_tab_columns : 

    ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等 

    dba_tab_columns: 

    ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等 

    获取表注释: 

    select * from user_tab_comments 

    user_tab_comments:table_name,table_type,comments 

    --相应的还有dba_tab_comments,all_tab_comments,这两个比user_tab_comments多了ower列。 

    获取字段注释: 

    select * from user_col_comments 

    user_col_comments:table_name,column_name,comments


    获取表约束

    SELECT constraint_name, table_name, r_owner, r_constraint_name 

    FROM all_constraints 

    WHERE table_name = 'table_name' and owner = 'owner_name'; 

    展开全文
  • Oracle查询表结构语句

    2011-04-19 10:02:43
    Oracle中使用SQL查询表结构 SELECT NULL AS table_cat, t.owner AS table_schem, t.table_name AS table_name, t.column_name AS column_name, DECODE (t.data_type, 'CHAR', 1, ...

    Oracle中使用SQL查询表结构

    SELECT   NULL AS table_cat, t.owner AS table_schem,
             t.table_name AS table_name, t.column_name AS column_name,
             DECODE (t.data_type,
                     'CHAR', 1,
                     'VARCHAR2', 12,
                     'NUMBER', 3,
                     'LONG', -1,
                     'DATE', 91,
                     'RAW', -3,
                     'LONG RAW', -4,
                     'BLOB', 2004,
                     'CLOB', 2005,
                     'BFILE', -13,
                     'FLOAT', 6,
                     'TIMESTAMP(6)', 93,
                     'TIMESTAMP(6) WITH TIME ZONE', -101,
                     'TIMESTAMP(6) WITH LOCAL TIME ZONE', -102,
                     'INTERVAL YEAR(2) TO MONTH', -103,
                     'INTERVAL DAY(2) TO SECOND(6)', -104,
                     'BINARY_FLOAT', 100,
                     'BINARY_DOUBLE', 101,
                     1111
                    ) AS data_type,
             t.data_type AS type_name,
             DECODE (t.data_precision,
                     NULL, t.data_length,
                     t.data_precision
                    ) AS column_size,
             0 AS buffer_length, t.data_scale AS decimal_digits,
             10 AS num_prec_radix, DECODE (t.nullable, 'N', 0, 1) AS nullable,
             NULL AS remarks, t.data_default AS column_def, 0 AS sql_data_type,
             0 AS sql_datetime_sub, t.data_length AS char_octet_length,
             t.column_id AS ordinal_position,
             DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable
        FROM all_tab_columns t
       WHERE t.owner LIKE 'ODS_USER' ESCAPE '/'
         AND t.table_name LIKE 'ODS_PRPCMAIN' ESCAPE '/'
      --   AND t.column_name LIKE :3 ESCAPE '/'
    ORDER BY table_schem, table_name, ordinal_position

     

    如果需要DDL可如下查询:

    1.SELECT DBMS_METADATA.get_ddl ('TABLE', 'ODS_PRPCMAIN_COEFF', 'ODS_USER')   
    2.FROM DUAL  
    

     

    展开全文
  • SELECT * FROM  OPENQUERY([192.168.1.5], 'select a.name 表名,b.name 字段名,c.name 字段类型,c.length 字段长度  ,case when b.isnullable=1 then 1 else 0 end 允许空, ...case when exists(SELECT 1 FROM...
    SELECT * FROM 
    OPENQUERY([192.168.1.5],


    'select a.name 表名,b.name 字段名,c.name 字段类型,c.length 字段长度 
    ,case when b.isnullable=1 then 1 else 0 end 允许空,
    case when exists(SELECT 1 FROM sysobjects where xtype=''PK'' and parent_obj=b.id and name in (
    SELECT name FROM sysindexes WHERE indid in(
    SELECT indid FROM sysindexkeys WHERE id = b.id AND colid=b.colid
    ))) then 1 else 0 end 主键
    from ELP.dbo.sysobjects a,ELP.dbo.syscolumns b,ELP.dbo.systypes c 
    where a.id=b.id
    and a.name=''T_Mカレンダー'' 
    and b.xtype=c.xtype'
    );
    展开全文
  • sqlserver 查询表结构语句

    千次阅读 2012-03-28 22:38:58
    SELECT CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名... CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 说明,  a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMN
  • =====查询表结构====== SELECT COLUMN_NAME 列名, COLUMN_TYPE 数据类型, # DATA_TYPE 字段类型, # CHARACTER_MAXIMUM_LENGTH 长度, IF(IS_NULLABLE='YES','Y','') 是否为空, COLUMN_DEFAULT 默认值, COLUMN_...
  • Oracle表结构查询语句

    千次阅读 2017-01-05 12:22:00
    Oracle表结构查询语句
  • 查询表结构的SQL语句

    2016-09-29 23:36:38
    如果需要查询表结构,SQL语句能否实现呢?下面就将为您介绍查询表结构的SQL语句的写法,希望对您学习SQL语句能够有所帮助。– 查询非系统数据库 Select name FROM Master.. SysDatabases where dbid>4 – 选择water...
  • 查询表结构sql语句

    千次阅读 2018-06-18 22:26:12
    select t2.comments as 字段中文名,t1.column_name as 字段名, t1.data_type||'('||t1.DATA_LENGTH||')' as 字段类型,'是' as 必填项, t2.comments as 取值范围及描述from user_tab_columns t1left join user_col_...
  • 分享一个SQL2005查询表结构的SQL语句,大家参考使用吧
  • 1.通过SQL语句查询表结构 SELECT COLUMN_NAME 字段名称, COLUMN_TYPE 数据类型, IF(IS_NULLABLE='NO','是','否') AS '必填', COLUMN_COMMENT 注释 FROM INFORMATION_SCHEMA.COLUMNS where --table_schema =...
  • 查询表结构的sql语句

    2011-07-01 16:11:02
    查询一个数据库下面所有的结构的T-sql语句
  • select table_name,column_name,column_comment from information_schema.columns where table_schema ='所在的库' and table_name = '要查看的表名' ; desc 表名; 根据条件查看表名 show tables like '...
  • SELECT a.column_name,a.data_type||'('||a.data_length||')',a.nullable,b.comments FROM ALL_TAB_COLUMNS a left join USER_COL_COMMENTS b on a.TABLE_NAME=b.table_name and a.column_name=b.column_name ...
  • SQL语句查询表结构

    2017-06-28 16:01:00
    SQL语句查询表结构 刚刚在做一个小项目,数据库中一张表有20来个字段,用我以前做的一个.NET实体类生成器一个一个的输入还是闲麻烦,于是打算找个时间来重新的改造一个那个.NET实体类,能够通过选择的数据库及用户...
  • GP数据库表结构查询语句

    万次阅读 2018-11-23 17:13:04
    一、GP数据库表结构查询SQL语句 查询时替换table_schema.table_name为所要查询的表即可。 select attname, -- 字段名 typname,-- 类型 CASE WHEN pg_truetypmod = -1 /* default typmod */ THEN null WHEN ...
  • 用Sql语句查询表结构

    2014-05-15 15:57:00
    关于表结构的查询可以用sp_help,...自己根据系统表写了一个查询表结构语句,方便使用 DECLARE @TableName VarChar(1000) SET @TableName = '要查询表结构的表名' SELECT 列名 = a.name , 类型 = CASE W...
  • 四种方式:①DESC 表名②DESCRIBE 表名③SHOW COLUMNS FROM 表名④SHOW CREATE TABLE 表名从以上图片结果中可以看出:show columns和desc和describe的结果是一样的,即:会将查询的每个字段的具体信息列出来,...
  • Postgresql、Hologres表结构相关查询语句1、SQL语句展示2、相关字段说明3、其他结构查询语句展示3.1、3.2、统计每张表的分区数量3.3、 1、SQL语句展示 SELECT DISTINCT a.attnum as num, a.attndims as dimension_...
  • Oracle查询表结构建表语句索引等

    千次阅读 2019-10-15 11:40:17
    --的备注说明: user_tab_comments --栏位的备注说明: user_col_...要收集此视图的统计信息,请使用ANALYZESQL语句或DBMS_STATS程序包。 --该视图与“ ALL_TAB_COLUMNS”不同之处在于,未过滤掉隐藏的列。 --使用...
  • 虽然DESC 命令能查看表的结构,但有时希望将表的结构输入到一张表中去或导入...查询自己能看到的表的表结构: SELECT T1.TABLE_NAME, T1.COLUMN_NAME, T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ')', T2.COMMENTS...
  • SQL查询表结构语句

    千次阅读 2007-04-18 16:26:00
    SQL查询表结构语句select ( case when a.colorder = 1 then d.name else end ) 表名, a.colorder 字段序号, a.name 字段名, ( case when COLUMNPROPERTY (a.id,a.name,isidentity) = 1 then √ else e
  • Oracle查询表结构的常用语句 博客分类:  Oracle Oracle表结构 1. 查询表结构基本信息  select * from user_tables t,user_tab_comments c  where c.table_name = t.table_name  and...
  • oracle使用sql语句查询表结构 SELECT t1.Table_Name || chr ( 13 ) || t3.comments AS “表名称及说明”, –t 3.comments AS “表说明”, t1.Column_Name AS “字段名称”, t1.DATA_TYPE || ‘(’ || t1.DATA_LENGTH...
  • 1. Mysql 数据库查询语句 show full columns from area desc area 2. Sql Server 数据库查询语句 sp_help Area 转载于:https://www.cnblogs.com/gylhaut/p/9208082.html
  • 1、查看表结构 desc student; 2、查看表的DDL语句 show create table student; 3、查看列的结构信息 select column_name,data_type,column_comment,column_key,extra,character_maximum_length,is_nullable,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 10,607
精华内容 4,242
关键字:

查询表结构语句