动态记录主表值被引用的行数

db中存在主键人力human，由于日积月累的数据，再加上前期设置主外键的混乱，有许多的子表里的键都属于human表中humanID,loginID,humanMonitorID的外键，并且有些表根本没有建立外键但值却存放着外键值。

现在要彻底删除某些主表的值，但在删除前需要找到这些值在其它表引用的数据有多行，所以就有了下面的解决方法：

 

IF OBJECT_ID ('p_JCXX_HumanQueryHumanID') IS NOT NULL
	DROP PROCEDURE p_JCXX_HumanQueryHumanID
GO

-- 传入的主健ID,返回的是引用行数
CREATE PROCEDURE p_JCXX_HumanQueryHumanID @humanID nvarchar(128),
@totalCount INT OUTPUT
AS SET NOCOUNT OFF
DECLARE @num INT;
DECLARE @sql NVARCHAR(512);
DECLARE @ptab NVARCHAR(50);
DECLARE @pcol NVARCHAR(50);
DECLARE @ftab NVARCHAR(50);
DECLARE @fcol NVARCHAR(50);
DECLARE @hmid NVARCHAR(50);	-- 人力监控ID
DECLARE @lgid NVARCHAR(50);	-- 人力登陆ID
CREATE TABLE #T(
	ptab NVARCHAR(128),
	pcol NVARCHAR(128),
	ftab NVARCHAR(128),
	fcol NVARCHAR(128)
); 
SELECT @hmid=humanMonitorID, @lgid=loginID FROM Human WHERE humanID = @humanID;
INSERT INTO #T
SELECT rtable.name ptab, cn.name pcol, ftable.name ftab, fkcn.name fcol
FROM sysforeignkeys INNER
    JOIN sysobjects ftable ON sysforeignkeys.fkeyid = ftable.id INNER
    JOIN sysobjects rtable ON sysforeignkeys.rkeyid = rtable.id INNER
    JOIN syscolumns fkcn ON sysforeignkeys.fkeyid = fkcn.id AND sysforeignkeys.fkey = fkcn.colid INNER
    JOIN syscolumns cn ON sysforeignkeys.rkeyid = cn.id AND sysforeignkeys.rkey = cn.colid
WHERE rtable.name IN ('Human')

-- 主表名,主列名,外表名,外列名(数据库未建立外键,但数值是属于外健,手动指定)
INSERT INTO #T (ptab,pcol,ftab,fcol)
	SELECT 'Human','humanID','MonitorModifyHistory','humanID' UNION
	SELECT 'Human','humanID','TypicalPart','designer';
BEGIN
	DECLARE datas CURSOR FOR SELECT * FROM #T 
	OPEN datas;
	FETCH next FROM datas INTO @ptab,@pcol,@ftab,@fcol
	WHILE @@FETCH_STATUS=0
	BEGIN
		IF @pcol ='humanID'
    		SET @sql = 'select @i=count(' + @fcol + ') from [' + @ftab + '] WHERE ' + @fcol + ' = '''+ @humanID + '''';
		IF @pcol ='loginID'
    		SET @sql = 'select @i=count(' + @fcol + ') from [' + @ftab + '] WHERE ' + @fcol + ' = '''+ @lgid + '''';
		IF @pcol ='humanMonitorID'
    		SET @sql = 'select @i=count(' + @fcol + ') from [' + @ftab + '] WHERE ' + @fcol + ' = '''+ @hmid + '''';
		IF @sql IS NOT NULL 
		BEGIN
    		EXEC sp_executesql @sql, N'@i int output',@num OUTPUT
    		IF @num >0
    			SET @totalCount = @totalCount + @num;
		END;
		FETCH next FROM datas INTO @ptab,@pcol,@ftab,@fcol
	END;
	CLOSE datas;
	DEALLOCATE datas;;
END