外键引用函数

---------------------------------------------------------------
--  SQL Server 2005
--      查看用户表的外键情况
--      @pmTableName   用户表名称
--      通过系统函数 sys.foreign_keys、sys.foreign_key_columns
--      和sys.columns三个系统函数实现
----------------------------------------------------------------    
IF OBJECT_ID('dbo.fnFindForeignKey','IF') IS NOT NULL
 DROP FUNCTION dbo.fnFindForeignKey;
GO

CREATE FUNCTION dbo.fnFindForeignKey(@pmTableName NVARCHAR(100))
 RETURNS TABLE
AS
 RETURN(
   SELECT OBJECT_NAME(K.parent_object_ID) AS 外键表,K.name AS 外键名称,
    C.Name AS 外键列,K.delete_referential_action_desc AS 删除项,
    K.update_referential_action_desc AS 更新项,OBJECT_NAME(K.Referenced_object_ID) AS 引用表,D.name 引用列
   FROM sys.foreign_keys K
    INNER JOIN sys.foreign_key_columns F
     ON K.Object_id=F.constraint_object_id
    INNER JOIN sys.columns C
     ON F.Parent_object_id=C.object_id AND F.parent_column_id=C.column_id
    INNER JOIN sys.columns D
     ON F.referenced_object_id=D.object_id AND F.referenced_column_id=D.column_id
   WHERE K.Parent_object_id=OBJECT_ID(@pmTableName)
   )

--事例
SELECT * FROM dbo.fnFindForeignKey('HumanResources.Employee')

 

Foreignkey.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15042150/viewspace-617687/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15042150/viewspace-617687/