sqlserver 查询表中的主键、外键列及外键表,外表中的主键列
2024-08-25 18:11:13
1、获取主键信息
EXEC sp_pkeys @table_name='{0}'
2、获取外键 方法二
SELECT Field=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) ,
FKTable=object_name(b.rkeyid),
FKKeyField=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
and object_name(b.fkeyid)='SYS_Page'
3、获取外键 方法二
select
oSub.name AS [子表名称],
fk.name AS [外键名称],
SubCol.name AS [子表列名],
oMain.name AS [主表名称],
MainCol.name AS [主表列名]
from
sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.object_id = SubCol.object_id
AND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.object_id = MainCol.object_id
AND fkCols.referenced_column_id = MainCol.column_id)
WHERE
oSub.name = '你的表名字'
--AND SubCol.name = '你的列名' 查询结果
最新文章
- inline-block元素vertical-align的问题分析
- An invalid character [32] was present in the Cookie value
- why happen ";WaitHandles must be less than or equal to 64";
- MultiLine Text光标停留在第一行
- sql语句常用说明与解析
- iOS开发UI篇—Date Picker和UITool Bar控件简单介绍
- 是不是content-type: text/html的数据包一到,浏览器就肯定刷新页面?
- PHP —— 读取文件到二维数组
- 左倾堆(一)之 图文解析 和 C语言的实现
- 初识 swift 封装轮播图
- Dzz任务板初版完成笔记-仿trello私有部署的一款轻量团队任务协作工具。
- html 文件动态加载.PDI 流程图
- LINUX2.4.x网络安全框架
- ILSpy .NET反编译工具下载地址
- JavaScript一个函数式编程-------求标准差
- ajax的四种type类型
- jq中的表单验证插件------jquery.validate
- MAMP显示文件列表
- 操作系统 - 死锁(Deadlock)的概述、条件、对策
- hdu-1054(二分图最大匹配)