List tables involved in a sql statement before it

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

List tables involved in a sql statement before it

Author

Topic

GazNewt
Starting Member

14 Posts

Posted - 2010-02-23 : 09:44:29

Is it possible in .NET to pass a sql statement to SQL server (eg; through SqlCommand.ExecuteReader) for parsing and return the tables involved in the statement and the type of operation. So for a statement like this :

select * from Table1
left outer join Table2 on Table1.id=Table2.foreignid;
delete from Table2 where date < 2009/12/12

SQL Server might return the tables involved like this :

Table1    Read
Table2    Read
Table2    Delete

So the statement isn't executed, it just returns the tables involved.

The reason I ask is that the application I am working on has application level table permissions that I want to apply on a table by table basis. The current method of parsing out the tables involved in a statement uses a regular expression and fails in anything other than simple statements. Obviously you can't really be using a regular expression for this kind of job

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 09:59:11

why dont you wrap the query in a procedure and then as your reqmnt make use of sp_depends procedure to look for tables reffered by your proc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GazNewt
Starting Member

14 Posts

Posted - 2010-02-23 : 10:14:36

quote:
Originally posted by visakh16

why dont you wrap the query in a procedure and then as your reqmnt make use of sp_depends procedure to look for tables reffered by your proc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks, the function that calles ExecuteReader() accepts a dynamic parameterized sql statement. It's basically a single function for all dynamic sql needed by the domain layer from the database layer. So there isn't a single sql statement I can wrap in a stored procedure. And since the sql statement must be parameterized I can't just pass in a single nvarchar parameter into the stored procedure for the statement to be run

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:37:40

sorry i didnt get that. what will be parameters you send?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GazNewt
Starting Member

14 Posts

Posted - 2010-02-23 : 10:51:49

quote:
Originally posted by visakh16

sorry i didnt get that. what will be parameters you send?

The statement could be anything with any number of parameters. I have a C# class used by the calling code which supplies a list of parameters and a parameterized sql statement. There are many places in the application where dynamic sql is required so they all go through a single function which handles all dynamic sql statements

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources