列说明:外键表编号,外键表名,外键表列号,外键列名,主键表编号,主键表名,主键表列号,主键列名
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