显示数据库表间的外键关系
来源:互联网 发布:mac退出全屏快捷键 编辑:程序博客网 时间:2024/04/29 20:16
select foreign_key=b.name,
foreign_key_table=c.name,
foreign_key_columns
=isnull((select name from syscolumns
where id=c.id and colid=a.fkey1),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey2),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey3),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey4),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey5),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey6),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey7),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey8),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey9),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey10),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey11),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey12),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey13),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey14),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey15),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey16),''),
primary_key_table=d.name,
primary_key_columns
=isnull((select name from syscolumns
where id=d.id and colid=a.rkey1),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey2),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey3),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey4),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey5),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey6),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey7),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey8),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey9),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey10),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey11),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey12),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey13),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey14),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey15),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey16),''),
cascade_update=case when (b.status/8192)%2=1 then 'Y' else '' end,
cascade_delete=case when (b.status/4096)%2=1 then 'Y' else '' end
from sysreferences a,sysobjects b,sysobjects c,sysobjects d
where b.parent_obj in(select id from sysobjects
where xtype='U' and name<>N'dtproperties')
and a.constid=b.id
and a.fkeyid=c.id
and a.rkeyid=d.id
order by foreign_key_table,foreign_key_columns
foreign_key_table=c.name,
foreign_key_columns
=isnull((select name from syscolumns
where id=c.id and colid=a.fkey1),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey2),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey3),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey4),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey5),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey6),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey7),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey8),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey9),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey10),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey11),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey12),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey13),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey14),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey15),'')
+isnull((select ','+name from syscolumns
where id=c.id and colid=a.fkey16),''),
primary_key_table=d.name,
primary_key_columns
=isnull((select name from syscolumns
where id=d.id and colid=a.rkey1),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey2),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey3),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey4),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey5),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey6),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey7),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey8),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey9),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey10),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey11),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey12),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey13),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey14),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey15),'')
+isnull((select ','+name from syscolumns
where id=d.id and colid=a.rkey16),''),
cascade_update=case when (b.status/8192)%2=1 then 'Y' else '' end,
cascade_delete=case when (b.status/4096)%2=1 then 'Y' else '' end
from sysreferences a,sysobjects b,sysobjects c,sysobjects d
where b.parent_obj in(select id from sysobjects
where xtype='U' and name<>N'dtproperties')
and a.constid=b.id
and a.fkeyid=c.id
and a.rkeyid=d.id
order by foreign_key_table,foreign_key_columns
- 显示数据库表间的外键关系
- 手动建立主从表关系数据库显示
- 数据库的关系键
- 关于数据库主从表、主键、外键的关系
- mysql 查询数据库的外键关系
- 数据库主键,外键的关系讲解
- 数据库表间关系
- 数据库表间关系
- 关系数据库表主键和外键
- 关系数据库中关系表间的连接(左连接,右连接,内连接,全连接,外连接) .
- 关系数据库中关系表间的连接(左连接,右连接,内连接,全连接,外连接)
- 关系数据库中关系表间的连接(内连接,外连接,左连接,右连接,全连接)
- 关系数据库中关系表间的连接(左连接,右连接,内连接,全连接,外连接)
- 关系数据库中关系表间的连接(左连接,右连接,内连接,全连接,外连接)
- 关系数据库关系的完整性
- sql server 删除数据库中所有的外键关系
- 数据库中的主键与外键的关系,通俗易懂
- mybatis对象之间映射关系以及数据库表建立的时候外键的添加
- Symbian OS中的消息存储与常用操作
- 程序员网站资源宝库
- [原创]三态的checkbox
- 《Java与模式》学习笔记之一――简单工厂模式(Simple Factory Pattern)
- SQL*Loader(日文版)
- 显示数据库表间的外键关系
- ngen本机映像生成器
- [Oracle]Plsql的一个例子
- 内存不足造成功能无效
- 我的Thinking in Java 3rd 的学习笔记
- 我的Blog开张咯~~~~~~~~~~
- 本科最后一门考试结束了
- 经典资料:CISCO协议总结大全
- linux常用命令