{"id":764,"date":"2011-02-08T08:19:46","date_gmt":"2011-02-08T13:19:46","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=764"},"modified":"2011-05-29T21:04:12","modified_gmt":"2011-05-30T02:04:12","slug":"parsing-affected-db-objects-net-version","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/","title":{"rendered":"Parsing affected db objects(.NET version)"},"content":{"rendered":"

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

Here is an article about Java version<\/a><\/p>\n

    \r\nParse 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.\r\n<\/pre>\nOne of the SQL files that needed to be parsed was:<\/p>\n\r\nUSE DB\r\ngo\r\nIF EXISTS (SELECT *\r\n           FROM   sys.objects\r\n           WHERE  object_id = Object_id(N'[dbo].[StoredProcedureB]')\r\n                  AND type IN ( N'P', N'PC' ))\r\n  DROP PROCEDURE [dbo].[StoredProcedureB]\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nIF NOT EXISTS (SELECT *\r\n               FROM   sys.objects\r\n               WHERE  object_id = Object_id(N'[dbo].[StoredProcedureB]')\r\n                      AND type IN ( N'P', N'PC' ))\r\n  BEGIN\r\n      EXEC dbo.Sp_executesql @statement = N'\r\n\r\n    CREATE PROCEDURE [dbo].[StoredProcedureB]\r\n       @Id bigint\r\n    AS\r\n    BEGIN\r\n    SET NOCOUNT ON;\r\n       SELECT  COUNT(DISTINCT o.Id)\r\n          FROM\r\n             Table1 n,\r\n             Table2 o\r\n          WHERE\r\n             n.Id = @Id AND\r\n             n.Id <> o.Id AND\r\n\r\n             n.Key = o.Key\r\n    END\r\n    '\r\n  END\r\nGO\r\nGRANT EXECUTE ON [dbo].[StoredProcedureB] TO [UserX]\r\nGO\r\n<\/pre>\nThe result he wanted to achieve was similar to this:<\/p>\n\r\nhas an IF exists drop for StoreProcedureB, \r\nthen create for StoredProcedureB, \r\nthen grant for StoredProcedureB\r\n<\/pre>\nPlease 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.<\/p>\nWe created a demo in C# to achieve what this user requested and got this:<\/p>\n\r\ndrop procedure: [dbo].[StoredProcedureB]\r\ncreate procedure: [dbo].[StoredProcedureB]\r\ngrant: [dbo].[StoredProcedureB] to [UserX]\r\n<\/pre>\nDownload this demo:C# demo<\/a>,Java demo<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n