获取Oracle、SqlServer、Access中表名、字段和主键(转)

最新推荐文章于 2022-09-01 15:11:29 发布

zhongxiucheng

最新推荐文章于 2022-09-01 15:11:29 发布

阅读量1.1k

点赞数

分类专栏： oracle SQL

oracle 同时被 2 个专栏收录

17 篇文章 0 订阅

订阅专栏

SQL

13 篇文章 0 订阅

订阅专栏

一、oracle

1、获取当前oracle数据库中的所有表

select table_name from user_tables

2、查询某个表中的字段名称、类型、精度、长度、是否为空

select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,NULLABLE
from user_tab_columns
where table_name ='YourTableName'

3、查询某个表中的主键字段名

select col.column_name
from user_constraints con, user_cons_columns col
where con.constraint_name = col.constraint_name
and con.constraint_type='P'
and col.table_name = 'YourTableName'

4、查询某个表中的外键字段名称、所引用表名、所应用字段名

select distinct(col.column_name),r.table_name,r.column_name
from
user_constraints con,
user_cons_columns col,
(select t2.table_name,t2.column_name,t1.r_constraint_name
from user_constraints t1,user_cons_columns t2
where t1.r_constraint_name=t2.constraint_name
and t1.table_name='YourTableName'
) r
where con.constraint_name=col.constraint_name
and con.r_constraint_name=r.r_constraint_name
and con.table_name='YourTableName'

5、如何从Oracle中取得表的注释

user_tab_comments;表注释

user_col_comments;表字段注释

　以上两个只能获取自己用户的表的注释信息，如果要访问自己

能够访问的其他用户的表，则需要使用：

all_tab_comments;表注释

all_col_comments;表字段注释

　当然，如果有DBA权限，则可以使用

dba_tab_comments;表注释

dba_col_comments;表字段注释

　 dba*和all*最好指定owner条件。user*没有该字段

user_tab_comments;表注释

user_col_comments;表字段注释

二、SQLServer

1、读取库中的所有表名

　　select name from sysobjects where xtype='u'

2、字段

SELECT c.name,t.name,c.xprec,c.xscale,c.isnullable
FROM systypes t,syscolumns c
WHERE t.xtype=c.xtype
AND c.id = (SELECT id FROM sysobjects WHERE name='YourTableName')
ORDER BY c.colid

3、主键（参考SqlServer系统存储过程sp_pkeys）

select COLUMN_NAME = convert(sysname,c.name)
from
sysindexes i, syscolumns c, sysobjects o
where o.id = object_id('[YourTableName]')
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
and (c.name = index_col ('[YourTableName]', i.indid, 1) or
c.name = index_col ('[YourTableName]', i.indid, 2) or
c.name = index_col ('[YourTableName]', i.indid, 3) or
c.name = index_col ('[YourTableName]', i.indid, 4) or
c.name = index_col ('[YourTableName]', i.indid, 5) or
c.name = index_col ('[YourTableName]', i.indid, 6) or
c.name = index_col ('[YourTableName]', i.indid, 7) or
c.name = index_col ('[YourTableName]', i.indid, 8) or
c.name = index_col ('[YourTableName]', i.indid, 9) or
c.name = index_col ('[YourTableName]', i.indid, 10) or
c.name = index_col ('[YourTableName]', i.indid, 11) or
c.name = index_col ('[YourTableName]', i.indid, 12) or
c.name = index_col ('[YourTableName]', i.indid, 13) or
c.name = index_col ('[YourTableName]', i.indid, 14) or
c.name = index_col ('[YourTableName]', i.indid, 15) or
c.name = index_col ('[YourTableName]', i.indid, 16)
)

4、外键

select t1.name,t2.rtableName,t2.name
from
(select col.name, f.constid as temp
from syscolumns col,sysforeignkeys f
where f.fkeyid=col.id
and f.fkey=col.colid
and f.constid in
( select distinct(id)
from sysobjects
where OBJECT_NAME(parent_obj)='YourTableName'
and xtype='F'
)
) as t1 ,
(select OBJECT_NAME(f.rkeyid) as rtableName,col.name, f.constid as temp
from syscolumns col,sysforeignkeys f
where f.rkeyid=col.id
and f.rkey=col.colid
and f.constid in
( select distinct(id)
from sysobjects
where OBJECT_NAME(parent_obj)='YourTableName'
and xtype='F'
)
) as t2
where t1.temp=t2.tem

 
       SELECT 
      
 
       [表名]= 
       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 
       '√' 
       else 
        '' 
       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 
       '√' 
       else 
       '' 
       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 
      
 
       --where d.name='TableName'    --如果只查询指定表,加上此条件 
      
 
       order 
        by 
       a.id,a.colorder 
      

 
     
          select 
         
 
            
          c. 
          name 
          as 
          cname,c.prec  
          AS 
          Prec,c.isnullable  
          AS 
          isnullable, 
         
 
            
          [IsPk]= 
          case 
          when 
          exists( 
          SELECT 
          1  
          FROM 
           sysobjects  
          where 
           xtype= 
          'PK' 
          and 
          parent_obj=c.id  
          and 
          name 
          in 
           ( 
         
 
                   
          SELECT 
          name 
          FROM 
           sysindexes  
          WHERE 
           indid  
          in 
          ( 
          SELECT 
          indid  
          FROM 
           sysindexkeys  
          WHERE 
           id = c.id  
          AND 
          colid=c.colid)))  
          then 
          '1' 
           else 
          '0' 
          end 
          , 
         
 
          [defaultval]= 
          isnull 
          (e.text, 
          '' 
          ), 
         
 
          t. 
          name 
          as 
          tname ,( 
          select 
          value  
          from 
           sys.extended_properties  
          as 
           ex  
          where 
          ex.major_id = c.id  
          and 
          ex.minor_id = c.colid)  
          as 
          notes  
         
 
          from 
         
 
            
          syscolumns  
          as 
          c  
          inner 
          join 
          sys.tables  
          as 
          ta  
          on 
           c.id=ta.object_id  
         
 
            
          inner 
          join  
          ( 
          select 
          name 
          ,system_type_id  
          from 
          sys.types  
          where 
          name 
          <> 
          'sysname' 
          )  
          as 
          t  
          on 
          c.xtype=t.system_type_id  
         
 
            
          left 
          join 
          syscomments e  
          on 
          c.cdefault=e.id 
         
 
          where 
         
 
            
          ta. 
          name 
          = 
          'TestTb' 
          order 
          by 
           c.colid 
         
 
   

三、Access

1、所有表清单

conn.Open();
dt= conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

2、表结构

conn.Open();

dtColumnsInfo = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, strTableName,null });

zhongxiucheng

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
获取Oracle、SqlServer、Access中表名、字段和主键(转)

一、oracle1、获取当前oracle数据库中的所有表select table_name from user_tables2、查询某个表中的字段名称、类型、精度、长度、是否为空 select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,NULLABLE from user_tab_columns wh
复制链接

扫一扫