CODEWITHSUNDEEP
c#.netsql
manu08
manu08

Reputation: 41

Get dependencies from SQL query

I'm trying to do some SQL query validation programatically in C# (without invoking the actual database). Essentially, I'd like a user to be able to enter a view, UDF, or SP and have its dependencies validated immediately. The user would be entering these into a custom tool for defining database objects.

Thus, if a user entered:

CREATE VIEW someView AS SELECT name, address FROM users

I could pull out the dependency of "users" and then check against my database object collections that are stored in memory (e.g., Tables, Views, etc...) to make sure that dependency exists in one of them. Keep in mind the actual views/UDFs/SPs entered into my custom app are very complex and parsing them myself is not desirable.

I'm currently trying to do this using Microsoft.Data.Schema.ScriptDom.Sql.TSql100Parser. This provides a parse method which returns a DOM representation of the query. However, this is a terribly complex DOM and I'm essentially having to write an entire parser just for it.

Any ideas/suggestions? Thanks!

Upvotes: 0

Views: 1388

Answers (1)

SQLMenace
SQLMenace

Reputation: 135021

There is a proc that you can use named sp_depends...however it is not failsafe because of deferred name resolution. There really is no safe way to do this, it has gotten a little better in the latest versions but still a pain in the neck

read this Do you depend on sp_depends (no pun intended) to see what I mean

Upvotes: 2

Related Questions