注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

樱之花

叶散的时候,你明白欢聚;花谢的时候,你明白青春.

 
 
 

日志

 
 
关于我

分类中“我的实验室”是我在日常工作中的一些知识总结,有些写的比较匆忙,可能大家在阅读时会产生困扰,后期有时间我会重新整理编辑,谢谢大家的到访,您们的支持是我前进的动力!

网易考拉推荐

获取Oracle、SqlServer表名、主键值、属性名  

2012-08-22 14:03:02|  分类: ORACLE |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

//获取表名,列名
SELECT   TOP   100   PERCENT   dbo.sysobjects.name   AS   e_table_name,  dbo.syscolumns.name   AS   e_column_name,  
            dbo.systypes.name   AS   e_column_type
FROM   dbo.sysobjects   INNER   JOIN
            dbo.syscolumns   ON   dbo.sysobjects.id   =   dbo.syscolumns.id   INNER   JOIN
            dbo.systypes   ON   dbo.syscolumns.xtype   =   dbo.systypes.xtype  
            where dbo.sysobjects.name = 'info_bridge'
ORDER   BY   dbo.sysobjects.name


/**
 *获取主键
 */
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)          
     )   


declare @table_name as varchar(max)
set @table_name = '你的表名'
select sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description
from sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name order by sys.columns.column_id

 

 


一、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)          
?     )   
BND-6DD3B1A0933

select   name   from   sysindexes   where   id   =   object_id( 'info_bridge')   and   status   =   2066
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.temp 三、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 });

 


测试:
select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,NULLABLE    
from user_tab_columns    
where table_name ='AFH_CUST';


select * from user_tables;

/* Formatted on 2012/06/05 11:17 (Formatter Plus v4.8.8) */
SELECT   a.table_name, b.comments, a.column_name, a.comments AS fieldcom,
         c.data_type, c.data_length
    FROM user_col_comments a, user_tab_comments b, user_tab_columns c
   WHERE a.table_name = b.table_name
     AND b.table_type = 'TABLE'
     AND a.table_name = c.table_name
     AND a.column_name = c.column_name
ORDER BY table_name, column_name;

SELECT * FROM  user_tab_comments   ;;
SELECT * FROM user_col_comments;

SELECT * FROM user_tab_columns;

select a.table_name,b.comments from user_tables a,user_tab_comments b where a.table_name=b.table_name

  评论这张
 
阅读(1214)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017