查询表主键外键信息的SQL
程序员文章站
2022-06-10 13:15:34
...
我的BSOOC里需要一个查询表主键外键信息的SQL,昨晚研究到凌晨1点,终于能实现这个目标:
Oracle:
select o.obj# as objectId, o.name AS tableName, oc.name AS constraintName,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', '?') as constraintType,
col.name AS columnName
from sys.con$ oc, sys.con$ rc,
sys.obj$ ro,sys.obj$ o, sys.obj$ oi,
sys.cdef$ c,
sys.col$ col, sys.ccol$ cc, sys.attrcol$ ac
where oc.con# = c.con#
and c.obj# = o.obj#
and c.rcon# = rc.con#( )
and c.enabled = oi.obj#( )
and c.robj# = ro.obj#( )
and c.type# != 8
and c.type# != 12 /* don't include log groups */
and c.con# = cc.con#
and cc.obj# = col.obj#
and cc.intcol# = col.intcol#
and cc.obj# = o.obj#
and col.obj# = ac.obj#( )
and col.intcol# = ac.intcol#( )
and o.name = 'your table'
SQL Server:
SELECT sysobjects.id objectId,
OBJECT_NAME(sysobjects.parent_obj) tableName,
sysobjects.name constraintName,
sysobjects.xtype AS constraintType,
syscolumns.name AS columnName
FROM sysobjects INNER JOIN sysconstraints
ON sysobjects.xtype in('C', 'F', 'PK', 'UQ', 'D')
AND sysobjects.id = sysconstraints.constid
LEFT OUTER JOIN syscolumns ON sysconstraints.id = syscolumns.id
WHERE OBJECT_NAME(sysobjects.parent_obj)='your table'
其它数据库还没时间去实现.
Oracle:
select o.obj# as objectId, o.name AS tableName, oc.name AS constraintName,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', '?') as constraintType,
col.name AS columnName
from sys.con$ oc, sys.con$ rc,
sys.obj$ ro,sys.obj$ o, sys.obj$ oi,
sys.cdef$ c,
sys.col$ col, sys.ccol$ cc, sys.attrcol$ ac
where oc.con# = c.con#
and c.obj# = o.obj#
and c.rcon# = rc.con#( )
and c.enabled = oi.obj#( )
and c.robj# = ro.obj#( )
and c.type# != 8
and c.type# != 12 /* don't include log groups */
and c.con# = cc.con#
and cc.obj# = col.obj#
and cc.intcol# = col.intcol#
and cc.obj# = o.obj#
and col.obj# = ac.obj#( )
and col.intcol# = ac.intcol#( )
and o.name = 'your table'
SQL Server:
SELECT sysobjects.id objectId,
OBJECT_NAME(sysobjects.parent_obj) tableName,
sysobjects.name constraintName,
sysobjects.xtype AS constraintType,
syscolumns.name AS columnName
FROM sysobjects INNER JOIN sysconstraints
ON sysobjects.xtype in('C', 'F', 'PK', 'UQ', 'D')
AND sysobjects.id = sysconstraints.constid
LEFT OUTER JOIN syscolumns ON sysconstraints.id = syscolumns.id
WHERE OBJECT_NAME(sysobjects.parent_obj)='your table'
其它数据库还没时间去实现.
推荐阅读
-
Sql Server 查询外键对应的Table 的通用方法
-
SQL语句删除和添加外键、主键的方法
-
如何区分SQL数据库中的主键与外键
-
获取MSSQL 表结构中字段的备注、主键等信息的sql
-
基于SQL2005 SQL2008 表结构信息查询升级版的详解(含外键信息)
-
牛客SQL练习-46-在audit表上创建外键约束,其emp_no对应employees_test表的主键id
-
SQL实战46.在audit表上创建外键约束,其emp_no对应employees_test表的主键id
-
数据库的主表,从表,主键,外键等之间的关系
-
sql语句:同一张表两个相同外键查询实例分享
-
Sql查询MySql数据库中的表名和描述表中字段(列)信息