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.
is there a easy in c# rather than using string manupulation to do this activity?
Add a comment |
3 Answers
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.
Comments
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).
1 Comment
sharath
Regular expressions cannot do this task, because SQL is not a regular language. You can do this, but not with a regular expression. You need a full-blown parser.