Declare table dependency in stored procedure when using T-SQL

Question

I am going to use dynamic sql in my stored procedure to remove some of the code duplication. But I see one big drawback for me in this case: I have rather big DB with lots of objects. My stored procedure is using few tables and since it is compiled I can find dependencies easily from Sql Server management studio.But when I rewrite it to dynamically build some of the repeating queries I will loose dependency possibility and next time when I would need to find who is using this table I will need to do raw text search in my code repository rather than asking sql server for dependency. This is probably small concern, but I would still like to try to find solution. So my question is: is there anything I can do to still have ability to see what dependencies my stored proc has? Like declare some dependencies upfront etc?

Answer 1

You can get dependencies to show up for the stored procedure for sections of code that never execute. For example, if you wanted to "declare" a dependency on a table named TestTable, you could use

CREATE PROC MyStoredProc
AS
    DECLARE @SQL VarChar(4000)
    SET @SQL = 'SELECT * FROM TestTable'
    EXEC (@SQL)


    RETURN
    SELECT 0 FROM TestTable -- Never executes but shows as dependency
END