代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
PLSQL
】
Oracle与SqlServer中获取所有字段、主键、外键的sql语
作者:
Dezai.CN
/ 发布于
2011/11/21
/
933
<div>Oracle:</div> 查询某个表中的字段名称、类型、精度、长度、是否为空 select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,NULLABLE from user_tab_columns where table_name ='YourTableName' 查询某个表中的主键字段名 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' 查询某个表中的外键字段名称、所引用表名、所应用字段名 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' <div>SQLServer中的实现: 字段: 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 主键(参考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) orc.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) ) 外键: 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 </div>
试试其它关键字
同语言下
.
查看某张表的表结构
.
oracle 集合操作
.
for 循环中倒序 用reverse
.
Oracle 临时表空间收缩
.
查看session及其对应运行的sql
.
Oracle常用查看表结构命令
.
Oracle Connect By Prior用法(实现递归查询)
.
重置排序 SEQUENCE
.
创建一个trigger
.
批量替换字段中的字符
可能有用的
.
查看某张表的表结构
.
oracle 集合操作
.
for 循环中倒序 用reverse
.
Oracle 临时表空间收缩
.
查看session及其对应运行的sql
.
Oracle常用查看表结构命令
.
Oracle Connect By Prior用法(实现递归查询)
.
重置排序 SEQUENCE
.
创建一个trigger
.
批量替换字段中的字符
Dezai.CN
贡献的其它代码
(
4037
)
.
多线程Socket服务器模块
.
生成随机密码
.
清除浮动样式
.
弹出窗口居中
.
抓取url的函数
.
使用base HTTP验证
.
div模拟iframe嵌入效果
.
通过header转向的方法
.
Session操作类
.
执行sqlite输入插入操作后获得自动编号的ID
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3