Parsing SQL statements ( get Column name used , where conditions etc ) in .NET

n my application , i have a textbox where user can paste Procedure , i need to parse this SQL and get the table names , joins , Columns ,Columns schema etc.

开发者_StackOverflow中文版is there a easy in c# rather than using string manupulation to do this activity?

Without using a complicated SQL Parser, it not that easy to retrieve table, column from SQL query correctly. Let's take this simple SQL for example:

SELECT a.deptno "Department", 
   a.num_emp/b.total_count "Employees", 
   a.sal_sum/b.total_sal "Salary"
FROM
(SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum
 FROM scott.emp
 GROUP BY deptno) a,
(SELECT COUNT(*) total_count, SUM(sal) total_sal
 FROM scott.emp) b

You are not only need to get column like: scott.emp.deptno in the subquery in from clause, but also can't take num_emp as a column, it just an alias in the subquery.

Just an example to let you know that string manipulation or RegEx doesn't work for this, you should fully parse SQL to get desired result precisely.

Here is a project you can look at for information for starter.

Edit - This might help you. If not, look into using RegEx.

The above project will not work for the big Procedure, procedures which contain more then 300 lines of Tsql , can you please provide any an alternative way to find it , as i need to use

using Microsoft.Data.Schema.ScriptDom.Sql;
using Microsoft.Data.Schema.ScriptDom;

asselmbles.

but now i am uable to get a code which uses the above assembles.

The main Result should be Table names and column names from a given store procedure( contain 300 lines of code).