Difficulty getting sp_executesql to work

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;

-- Execu

I think you are attempting to get a list of tables that contain rows referenced in another table. I think you want to encapsulate this into a re-usable object, such as a stored procedure.

I created a simple schema to test your code, as such:

USE TempDB;
CREATE TABLE dbo.T1
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(255)
);
CREATE TABLE dbo.T2
(
    ID INT NOT NULL FOREIGN KEY REFERENCES dbo.T1(ID)
    , SomeOtherData VARCHAR(255)
);

INSERT INTO dbo.T1 VALUES (1, 'Test');
INSERT INTO dbo.T2 VALUES (1, 'Test2');

The following shows the table T2 as output, which I think is what you are attempting to do.

CREATE PROCEDURE dbo.GetReferencedTables
(
    @TableName SYSNAME
    , @RowID INT
)
AS
BEGIN
    DECLARE @OutputTable TABLE
    (
            TableName SYSNAME
    );
    DECLARE @Command nvarchar(max);

    SELECT @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;

    --Totally unnecessary to put the results into a table variable
    --unless you want to further manipulate or use the results
    INSERT INTO @OutputTable
    EXEC sp_executesql @Command;

    SELECT * /* NEVER use SELECT * unless you're me. 
            see http://bit.ly/1nRziYq for reasons.
            */
    FROM @OutputTable
    ORDER BY TableName;
END
GO
EXEC dbo.GetReferencedTables 'T1',1;

Output:

StackExchange Database Administrators Q#72664, answer score: 3