Parsing affected db objects(.NET version)

This article was based on a demo requested by a user of General SQL Parser. The following is what he needed to achieve:

Here is an article about Java version

Parse out the affect SQL object in a .SQL file. Meaning, read some .SQL file which can have many different types of SQL (select, insert, create, drop, etc). And then determine what is being affected.

One of the SQL files that needed to be parsed was:

USE DB
go
IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = Object_id(N'[dbo].[StoredProcedureB]')
                  AND type IN ( N'P', N'PC' ))
  DROP PROCEDURE [dbo].[StoredProcedureB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = Object_id(N'[dbo].[StoredProcedureB]')
                      AND type IN ( N'P', N'PC' ))
  BEGIN
      EXEC dbo.Sp_executesql @statement = N'

    CREATE PROCEDURE [dbo].[StoredProcedureB]
       @Id bigint
    AS
    BEGIN
    SET NOCOUNT ON;
       SELECT  COUNT(DISTINCT o.Id)
          FROM
             Table1 n,
             Table2 o
          WHERE
             n.Id = @Id AND
             n.Id <> o.Id AND

             n.Key = o.Key
    END
    '
  END
GO
GRANT EXECUTE ON [dbo].[StoredProcedureB] TO [UserX]
GO

The result he wanted to achieve was similar to this:

has an IF exists drop for StoreProcedureB, 
then create for StoredProcedureB, 
then grant for StoredProcedureB

Please note that “CREATE PROCEDURE [dbo].[StoredProcedureB]” is not a sql statement in this script, but a parameter of an EXEC statement, so it’s really not that easy to pick up this information from the SQL script.

We created a demo in C# to achieve what this user requested and got this:

drop procedure: [dbo].[StoredProcedureB]
create procedure: [dbo].[StoredProcedureB]
grant: [dbo].[StoredProcedureB] to [UserX]

Download this demo:C# demo,Java demo