查询 数据库 中 表列 主外键关联

列说明:外键表编号,外键表名,外键表列号,外键列名,主键表编号,主键表名,主键表列号,主键列名

ALTER PROCEDURE [dbo].[P_SltTbColsPKey3]

	@tb nchar(32)

AS

BEGIN

--查询表的所有主键表 列...

select fk.parent_object_id as fCod,object_name(fk.parent_object_id) as fTb,fk.parent_column_id as fIdx,c1.name as fCol,

fk.referenced_object_id as pCod,object_name(fk.referenced_object_id) as pTb,fk.referenced_column_id as pIdx,c2.name as pCol 

from sys.foreign_key_columns fk

join sys.tables t on t.object_id=object_id(@tb)and fk.parent_object_id=t.object_id

join syscolumns c1 on c1.id=fk.parent_object_id and c1.colorder=fk.parent_column_id

join syscolumns c2 on c2.id=fk.referenced_object_id and c2.colorder=fk.referenced_column_id



END

存储过程调用方法: exec dbo.存储过程名称 ‘str‘,0,‘c‘<br>例: exec dbo.P_SltTbColsBasInf4 ‘master‘

交流 QQ : 2412366909@qq.com

手机号码:177-7499-4428

注:本人使用过程中暂未发现造成存取不正确的问题

*以上代码集合于网上查找的相关 SQL 资料 

ALTER PROCEDURE [dbo].[P_SltTbColsPKey3]@tb nchar(32)ASBEGIN--查询表的所有主键表 列...select fk.parent_object_id as fCod,object_name(fk.parent_object_id) as fTb,fk.parent_column_id as fIdx,c1.name as fCol,fk.referenced_object_id as pCod,object_name(fk.referenced_object_id) as pTb,fk.referenced_column_id as pIdx,c2.name as pCol from sys.foreign_key_columns fkjoin sys.tables t on t.object_id=object_id(@tb)and fk.parent_object_id=t.object_idjoin syscolumns c1 on c1.id=fk.parent_object_id and c1.colorder=fk.parent_column_idjoin syscolumns c2 on c2.id=fk.referenced_object_id and c2.colorder=fk.referenced_column_id
END

查询 数据库 中 表列 主外键关联

上一篇:centos安装Mysql


下一篇:数据库之MySQL的DQL语句(查询语句)