Dynamic SQL and sys.sql_dependencies

  • Hi,

    When writing a procedure using dynamic SQL (cause of parameters that may not be used, ...) SQL Server doesn't "fill" the sys.sql_dependencies cause it doesn't know what tables are used (the query is a string....).

    I don't know if its important for SQL Server to know the dependencies correctly but if they exist there's a reason :-), even if just for when dropping an object warn what other objects use it...

    So I wonder if it's a good approach to add at the begging of the procedure, triggers, ... an IF condition that's never true (1 = 0) and use the tables involved on the dynamic SQL?

    CREATE PROCEDURE GetProducts

    @ID INT = NULL,

    @Name VARCHAR(10) = NULL

    AS

    BEGIN

    DECLARE @query NVARCHAR(MAX), @params NVARCHAR(MAX)

    IF 1 = 0

    BEGIN

    -- add other tables that might be used

    SELECT 1 FROM Products WHERE 1 = 0

    END

    SET @params = '@IdIn INT, @NameIn VARCHAR(10)'

    SET @query = 'SELECT * FROM dbo.Products WHERE 1 = 1'

    IF @ID IS NOT NULL SET @query = @query + ' AND ProductID = @IdIn'

    IF @Name IS NOT NULL SET @query = @query + ' AND ProductName = @NameIn'

    EXEC sp_executesql @query, @params, @IdIn = @Id, @NameIn = @Name

    END

    The if condition is just a "Constant scan" that's never achieved so the statements inside are never executed...

    Thanks,

    Pedro



    If you need to work better, try working less...

  • No. Just no.

    There's a million ways to mess up dependencies and sys.sql_dependencies is notorious for being wrong. The improved DMVs in 2008 and above are far better, still not perfect (dynamic SQL). Don't make your code more complex for no good reason

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    Just tried the sys.dm_sql_referenced_entities but the dynamic SQL isn't analyzed...

    The following code returns info for the "normal" GetProducts that uses ProductId = COALESCE(@ID, ProductId) AND ... but as you say in a post using dynamic SQL is best for these situations: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,

    referenced_minor_id, referenced_class_desc

    FROM sys.dm_sql_referenced_entities ('dbo.GetProducts2', 'OBJECT')

    I was just trying a way of getting the dependencies correct but with dynSQL there isn't much chance on doing it... or I just don't know how

    Thanks,

    Pedro



    If you need to work better, try working less...

  • PiMané (10/31/2012)


    Hi,

    Just tried the sys.dm_sql_referenced_entities but the dynamic SQL isn't analyzed...

    The following code returns info for the "normal" GetProducts that uses ProductId = COALESCE(@ID, ProductId) AND ... but as you say in a post using dynamic SQL is best for these situations: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,

    referenced_minor_id, referenced_class_desc

    FROM sys.dm_sql_referenced_entities ('dbo.GetProducts2', 'OBJECT')

    I was just trying a way of getting the dependencies correct but with dynSQL there isn't much chance on doing it... or I just don't know how

    Thanks,

    Pedro

    Did you ever figure out the drawback to your strategy? It seemed to make sense to me. What is the downside?

  • Correct, you can't get dependencies in any automated way with dynamic SQL. That's one of its downsides.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • patrickmcginnis59 (10/31/2012)


    Did you ever figure out the drawback to your strategy? It seemed to make sense to me. What is the downside?

    Well.. when someone like Gail says "No. Just no." you just have "to go with it" 🙂

    I'm just updating my skills since I stopped "real" DBA for 3 years...

    But when someone like Gail or Grant or Glenn says something like that you don't question it. Not a sheep following the herd blindly but just learning with the best...

    Pedro



    If you need to work better, try working less...

  • Basically it's additional complexity in the procedures solely for correct dependencies.

    Do you use those dependencies in your development? Do you have any process or procedure that requires that the dependencies be absolutely correct? Do you gain anything at all for this additional complexity?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/31/2012)


    Basically it's additional complexity in the procedures solely for correct dependencies.

    Do you use those dependencies in your development? Do you have any process or procedure that requires that the dependencies be absolutely correct? Do you gain anything at all for this additional complexity?

    In my case I'd need it to build a dependency diagram for all the database objects.

    Probably, for now, I'll build the possible diagram from the dependencies and add a '?' whenever there's an exec sp_executesql...

    Thanks,

    Pedro



    If you need to work better, try working less...

  • PiMané (10/31/2012)


    patrickmcginnis59 (10/31/2012)


    Did you ever figure out the drawback to your strategy? It seemed to make sense to me. What is the downside?

    Well.. when someone like Gail says "No. Just no." you just have "to go with it" 🙂

    LOL duly noted!

  • I realise this is an old post, but I came across it when trying to look for a way to find dependencies within dynamic SQL. One thing I saw suggested on this stackoverflow post was a the free SQL Search tool from RedGate.

    I think when used in conjunction with the DMVs listed above, it can certainly help you find your dependencies.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply