Alex Aza's comment solution here generates all names of tables with records referencing a specified input record. I would like to adapt Alex's solution into a table-valued function. I'm having trouble seeing how to parameterize his dynamic SQL code correctly. The single-vs-double quotes are tripping me up, for example.
A typical value of @Command from Alex's solution is as follows:
SELECT 'PrimaryTable' WHERE EXISTS( SELECT * FROM PrimaryTable WHERE ForeignKey_PrimaryTable = 999 ) UNION ALL SELECT 'ForeignTable_A' WHERE EXISTS( SELECT * FROM ForeignTable_A WHERE ForeignKey_PrimaryTable = 999 ) UNION ALL SELECT 'ForeignTable_B' WHERE EXISTS( SELECT * FROM ForeignTable_B WHERE ForeignKey_PrimaryTable = 999 );
I think I've set up the framework correctly & would appreciate any assistance with assignment of the @Command variable.
Thanks so much for your consideration!
My partial solution is as follows:
-- Declarations needed for sp_executesql declare @ExecParameterDefinition NVARCHAR(1024) declare @ExecOutput TABLE(TableName NVARCHAR(1024)); declare @CapturedOutputTable TABLE(TableName NVARCHAR(1024)); SET @ExecParameterDefinition = N'@RowId int, ' + N'@TableName sysname, ' + N'@ExecOutput TABLE(TableName NVARCHAR(1024)) OUTPUT' -- Alex Aza's solution declare @Command nvarchar(max) -- This statement needs to be completely quoted as an executable string: SET @Command = isnull(@Command + ' union all ', '') + 'select ''' + object_name(parent_object_id) + ''' where exists(select * from ' + object_name(parent_object_id) + ' where ' + col.name+ ' = ' + cast(@RowId as varchar) + ')' from sys.foreign_key_columns fkc join sys.columns col on fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id where object_name(referenced_object_id) = @TableName; -- Display constructed SQL command: SELECT @Command; -- Execute dynamic SQL & capture output into a table variable: EXEC sp_executesql @Command, @ExecParameterDefinition, @RowId = 999, @TableName = 'PrimaryTable', @CapturedOutputTable = @ExecOutput OUTPUT -- Display captured results: select * FROM @CapturedOutputTable
UPDATE : Thanks, Paul White, for pointing out that dynamic-SQL can't be called from within a function. It appears though that I could call sp_executesql from a stored procedure & simply return a bit value indicating whether sp_executesql returned any results or not. Does this make my problem tractable?
UPDATE 2 : I finished parameterizing my code solution, which was incomplete in my original post. Solutions provided here, such as Hannah Vernon's below, can now be quickly tested. I also included Hannah's suggestion to "SELECT @Command".
Running this code with Hannah's suggested solution seems to hit a syntax error in the call to sp_executesql:
(1 row(s) affected) Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'TABLE'. (0 row(s) affected) (1 row(s) affected)
I can only imagine that the @ExecOutput table variable declaration must be incorrect within @ExecParameterDefinition..? Could someone please correct me on this as well?