InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 02:28:18 GMT20http://www.sqlservercentral.com/Forums/Topic1379235-391-1.aspxI 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 [url=http://stackoverflow.com/a/12044348/685760]stackoverflow post[/url] 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.Wed, 13 Feb 2013 19:44:50 GMTChris Mooringhttp://www.sqlservercentral.com/Forums/Topic1379235-391-1.aspx[quote][b]PiMané (10/31/2012)[/b][hr][quote][b]patrickmcginnis59 (10/31/2012)[/b][hr]Did you ever figure out the drawback to your strategy? It seemed to make sense to me. What is the downside?[/quote]Well.. when someone like Gail says "No. Just no." you just have "to go with it" :-)[/quote]LOL duly noted! Wed, 31 Oct 2012 11:15:27 GMTpatrickmcginnis59http://www.sqlservercentral.com/Forums/Topic1379235-391-1.aspx[quote][b]GilaMonster (10/31/2012)[/b][hr]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?[/quote]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,PedroWed, 31 Oct 2012 10:20:21 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1379235-391-1.aspxBasically 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?Wed, 31 Oct 2012 10:04:16 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1379235-391-1.aspx[quote][b]patrickmcginnis59 (10/31/2012)[/b][hr]Did you ever figure out the drawback to your strategy? It seemed to make sense to me. What is the downside?[/quote]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...PedroWed, 31 Oct 2012 09:48:31 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1379235-391-1.aspxCorrect, you can't get dependencies in any automated way with dynamic SQL. That's one of its downsides.Wed, 31 Oct 2012 09:42:51 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1379235-391-1.aspx[quote][b]PiMané (10/31/2012)[/b][hr]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: [url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url][code="sql"]SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name, referenced_minor_id, referenced_class_descFROM sys.dm_sql_referenced_entities ('dbo.GetProducts2', 'OBJECT')[/code]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[/quote]Did you ever figure out the drawback to your strategy? It seemed to make sense to me. What is the downside?Wed, 31 Oct 2012 09:34:46 GMTpatrickmcginnis59http://www.sqlservercentral.com/Forums/Topic1379235-391-1.aspxHi,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: [url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url][code="sql"]SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name, referenced_minor_id, referenced_class_descFROM sys.dm_sql_referenced_entities ('dbo.GetProducts2', 'OBJECT')[/code]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,PedroWed, 31 Oct 2012 08:28:29 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1379235-391-1.aspxNo. 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 reasonWed, 31 Oct 2012 07:44:53 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1379235-391-1.aspxHi,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?[code="SQL"]CREATE PROCEDURE GetProducts@ID INT = NULL,@Name VARCHAR(10) = NULLASBEGIN 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 = @NameEND[/code]The if condition is just a "Constant scan" that's never achieved so the statements inside are never executed...Thanks,PedroWed, 31 Oct 2012 04:35:49 GMTPiMané