Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic SQL and sys.sql_dependencies Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 4:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1379235
Posted Wednesday, October 31, 2012 7:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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

Post #1379308
Posted Wednesday, October 31, 2012 8:28 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1379352
Posted Wednesday, October 31, 2012 9:34 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
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?
Post #1379396
Posted Wednesday, October 31, 2012 9:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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

Post #1379404
Posted Wednesday, October 31, 2012 9:48 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1379407
Posted Wednesday, October 31, 2012 10:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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

Post #1379412
Posted Wednesday, October 31, 2012 10:20 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1379420
Posted Wednesday, October 31, 2012 11:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
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!
Post #1379455
Posted Wednesday, February 13, 2013 7:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:37 AM
Points: 1, Visits: 30
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.
Post #1419798
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse