Determine if the data in the table has been used in other tables!!

Data

CREATE PROCEDURE dbo. Systemfuntion_hasbeused
@PTableName Varchar (100)--the name of the primary key table to check whether the data is being used
@PKValue INT,--the value of the primary key field
@IsUsed INT OUTPUT
As

DECLARE @TableName VARCHAR (100)--name of the foreign key table
DECLARE @FKName VARCHAR (100)--foreign key field name
DECLARE @FKey INT

DECLARE @SQL VARCHAR (8000)

SET @sql = ' SELECT 0 as colcount into #tempTableCol UNION '

DECLARE cursorfktable CURSOR for
SELECT B.[name] as TableName, A.fkey
From Sysforeignkeys a INNER join sysobjects B on a.fkeyid=b.[id] INNER join sysobjects C on A.rkeyid=c.[id]
WHERE c.[name]= @PTableName

OPEN cursorfktable
FETCH NEXT from cursorfktable into @TableName, @FKey

While @ @FETCH_STATUS = 0
BEGIN

SELECT @FKName = A.[name] from syscolumns a INNER join sysobjects B on A.[id]=b.[id] WHERE b.[name]= @TableName and @FKey = A.colid

SET @sql = @sql + ' SELECT case if COUNT (*) =0 THEN 0 ELSE 1 End from ' + @TableName + ' WHERE ' + @FKName + ' = ' + CAST (@p Kvalue as VARCHAR (100))
SET @sql = @sql + ' UNION '

FETCH NEXT from cursorfktable into @TableName, @FKey
End
Close Cursorfktable
Deallocate cursorfktable

SELECT @sql = Left (@sql, Len (@sql)-6)
EXEC (@sql)
SELECT @IsUsed = @ @ROWCOUNT-1
Go


How to use:

/*********** gets whether the current primary key value has been used by another table (@HasPKValueBeUsed as haspkvaluebeused) *************/
DECLARE @HasPKValueBeUsed INT
EXECUTE systemfuntion_hasbeused ' Tlsubjectplan ', @SubjectPlanID, @IsUsed = @HasPKValueBeUsed OUTPUT
/*******************************************************************************/