BTW:

If you know the name of the FK constraint, you can check to see if it exists by doing...

IF object_id(@fkname) IS NOT NULL
PRINT 'Exists'
ELSE
PRINT 'Not Exists'
ENDIF

BOb



>I'm working on an SP to generically test for the existance of foreign key references in child tables, and have run into a little snag. Since it has to be generic - I just want to pass a table name and the FK value to check - I'm using a dynamically-built SQL statement and SP_EXECUTESQL to do part of the work. However, there is no way to direct the results of the query into, say, a temporary table for later usage. Instead of getting a list of child tables that have the foreign key in them, I can only get the first occurance.
>
>Here's the SP so far:
>
>

>CREATE procedure test_fk_locator
>@tablename varchar(36), @pkvalue uniqueidentifier
>
>as
>
>set nocount on
>
>declare @sqlstring nvarchar(500), @ParmDefinition NVARCHAR(500),
>	@fktablename varchar(36), @fkcolumnname varchar(36),
>	@fk_value uniqueidentifier,
>	@returntablename varchar(36), @returncolumnname varchar(36)
>
>declare fk_cursor CURSOR
>for
>SELECT  TOP 100 PERCENT
>	sysobjects_1.name AS fktablename,
>	dbo.syscolumns.name AS fkcolumnname
>FROM    dbo.sysobjects
>INNER JOIN
>	dbo.sysforeignkeys ON dbo.sysobjects.id = dbo.sysforeignkeys.rkeyid
>INNER JOIN
>	dbo.sysobjects sysobjects_1 ON dbo.sysforeignkeys.fkeyid = sysobjects_1.id
>INNER JOIN
>	dbo.syscolumns ON sysobjects_1.id = dbo.syscolumns.id AND
>	dbo.sysforeignkeys.fkey = dbo.syscolumns.colid
>WHERE	(dbo.sysobjects.name = @tablename)
>ORDER BY
>	sysobjects_1.name, dbo.syscolumns.name
>
>open fk_cursor
>FETCH NEXT FROM fk_cursor
>INTO @fktablename, @fkcolumnname
>
>WHILE @@FETCH_STATUS = 0
>BEGIN
>	set @sqlstring = N'declare @retvalue uniqueidentifier;' +
>	'select top 1 @retvalue = ' +
>		@fkcolumnname  + ' from ' +
>		@fktablename + ' where ' +
>		@fkcolumnname  + ' = @parentpk;' +
>	'if @retvalue is not null select @rettablename as tablefailedon'	
>	SET @ParmDefinition = N'@parentpk uniqueidentifier, ' +
>		'@rettablename varchar(36)'
>	EXECUTE sp_executesql @SQLString, @ParmDefinition,
>                      @parentpk = @pkvalue, @rettablename = @fktablename	
>	if @@ROWCOUNT > 0
>		break
>
>	FETCH NEXT FROM fk_cursor
>		INTO @fktablename, @fkcolumnname
>end
>
>close fk_cursor
>deallocate fk_cursor
>GO
>
>

>
>If I don't BREAK the cursor cycling, I end up with a bunch of single-row result sets. In a dim corner of what passes for my brain is the thought that a SELECT can be performed with something like a "NORESULT" clause (to just fire the statement without generating result set - all I want to do is prove or disprove the use of the value as a foreign key) but I can't find the syntax to do that in the BOL.
>
>Anybody got any fresh ideas? I'm kinda stumped at this point.