3

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 Max Vernon's below, can now be quickly tested. I also included Max's suggestion to "SELECT @Command".

Running this code with Max'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?

3

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:

enter image description here

| improve this answer | |
  • 1
    Thank you Max for taking a look. I have edited my code framework above to allow solutions such as yours to be quickly tested. As far as the desired output, it's simply proof-of-concept that the @CapturedOutputTable contains the relevant foreign table names. – Brad Stiritz Jul 29 '14 at 17:41
  • 1
    Fantastic, Max, your solution works great! Thank you so much, I really appreciate this. I was at an impasse at a higher level & now can move forward quickly. Best wishes, Brad – Brad Stiritz Jul 29 '14 at 17:52
  • hah, I just removed that from my answer. It actually wouldn't prevent anything from working since it would only ever have an impact if you left the table name blank when calling the stored proc. As in EXEC dbo.GetReferencedTables @RowID = 1; – Max Vernon Jul 29 '14 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.