CRUD Reportor

Database consultant, DBA and SQL developers always need to read SQL scripts to find out the tables have Create, Read, Update, Delete and Insert operations against them. If SQL scripts is complex and large than 100 lines, then it's almost impossible to do that without help with a tool. If you need to analyze hundred's of SQL scripts, then you will need a tool or API help you to do that definitely.

General SQL Parser can help you to do that automatically with less than 100 lines C# code(The code is ready here). This code can search all tables in Oracle PLSQL scripts(including procedure/function/package), function/procedure of SQL Server, MySQL and DB2. No matter how complex and large the SQL script is, as long as it's syntax valid, this CRUD reportor can process that well. Of course, you can modify this code to fit your own need.

Take the following simple SQL for example, CRUD Reportor can tell you that in dbo.Fn_findreports function, table Employee, Contact and DirectReports has been selected twice, and table retFindReports has been inserted once.

 

CREATE FUNCTION dbo.Fn_findreports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE (
  EmployeeID INT PRIMARY KEY NOT NULL,
  Name       NVARCHAR(255) NOT NULL )
AS
  BEGIN
      WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
           AS (SELECT c.FirstName,
                      e.EmployeeID
               FROM   HumanResources.Employee AS e
                      JOIN Person.Contact AS c
                        ON e.ContactID = c.ContactID
               WHERE  e.EmployeeID = @InEmpID
               UNION ALL
               SELECT c.FirstName,
                      e.EmployeeID,
               FROM   HumanResources.Employee AS e
                      JOIN Person.Contact AS c
                        ON e.ContactID = c.ContactID
                      JOIN DirectReports AS d
                        ON e.ManagerID = d.EmployeeID)
      -- copy the required columns to the result of the function
      INSERT retFindReports
      SELECT EmployeeID,
             Name
      FROM   DirectReports

      RETURN
  END;

GO 

 

Questions?

If you have any questions about General SQL Parser and this CRUD reportor, You can always send us an email and we'll get back to you within 24 hours.

 

Any other benefits can I get by using this SQL Parser?

Yes, of course. We collect all kinds of examples in this page to illustrate how and why you want to use general sql parser, hope it can help you to make better use of this library.