CREATE Procedure DBO.SystemFUNAME VARCHAR (100), - To check if the data is used by the primary key table name @pkvalue int, - the value of the primary key field @isused int outputas declare @tablename varchar (100) - foreign key table name DECLARE @FKName VARCHAR (100) - the name of the foreign key field 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 @ 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 WHEN COUNT (*) = 0 THEN 0 ELSE 1 End from' @Tablename 'Where' @fkname '=' Cast ( @Pkvalue as varchar (100)) set @SQL = @SQL 'Union' fetch next from cursorfktable @Tablen Ame, @ fkey endocate cursorfktable deallocate cursorfktable
Select @sql = left (@ SQL, LEN (@SQL) -6) EXEC (@SQL) SELECT @ISUSED = @@ rowcount-1go
Instructions:
/ *********** Gets whether the current primary key value is used by other tables (@haspkvaluebeuseced ashasis ************ / declare @haspkvaluebeused int execute systemfuntion_haasbeused ' TLSUBJECTPLAN, @ subjectplanid, @ iSused = @ HaspkValueBeused output / ********************************************************* ************************************************ /
