Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic SQL and sys.sql_dependencies


Dynamic SQL and sys.sql_dependencies

Author
Message
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47254 Visits: 44380
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


PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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...
patrickmcginnis59
patrickmcginnis59
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 2333
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47254 Visits: 44380
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


PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47254 Visits: 44380
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


PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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...
patrickmcginnis59
patrickmcginnis59
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 2333
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!
Chris Mooring
Chris Mooring
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 33
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search