Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# 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