{"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":"<p>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<p><a href=\"http:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objectsjava-version\/\">Here is an article about Java version<\/a><\/p>\n<pre>\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>\n<p>One of the SQL files that needed to be parsed was:<\/p>\n<pre>\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>\n<p>The result he wanted to achieve was similar to this:<\/p>\n<pre>\r\nhas an IF exists drop for StoreProcedureB, \r\nthen create for StoredProcedureB, \r\nthen grant for StoredProcedureB\r\n<\/pre>\n<p>Please note that &#8220;CREATE PROCEDURE [dbo].[StoredProcedureB]&#8221; is not a sql statement in this script, but a parameter of an EXEC statement, so it&#8217;s really not that easy to pick up this information from the SQL script.<\/p>\n<p>We created a demo in C# to achieve what this user requested and got this:<\/p>\n<pre>\r\ndrop procedure: [dbo].[StoredProcedureB]\r\ncreate procedure: [dbo].[StoredProcedureB]\r\ngrant: [dbo].[StoredProcedureB] to [UserX]\r\n<\/pre>\n<p>Download this demo:<a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/csharp\/affectedObject\/affectedObject.cs\">C# demo<\/a>,<a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/java\/getcrud\/getcrud.java\">Java demo<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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]&#8217;) AND type IN ( N&#8217;P&#8217;, N&#8217;PC&#8217; )) 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":"<!-- This site is optimized with the Yoast SEO plugin v19.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Help you to make better use of General SQL Parser<\/title>\n<meta name=\"description\" content=\"Parsing affected db objects(.NET version)\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Help you to make better use of General SQL Parser\" \/>\n<meta property=\"og:description\" content=\"Parsing affected db objects(.NET version)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2011-05-30T02:04:12+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/\",\"name\":\"Help you to make better use of General SQL Parser\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2011-02-08T13:19:46+00:00\",\"dateModified\":\"2011-05-30T02:04:12+00:00\",\"description\":\"Parsing affected db objects(.NET version)\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"List of demos illustrate how to use general sql parser\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Parsing affected db objects(.NET version)\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Help you to make better use of General SQL Parser","description":"Parsing affected db objects(.NET version)","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/","og_locale":"en_US","og_type":"article","og_title":"Help you to make better use of General SQL Parser","og_description":"Parsing affected db objects(.NET version)","og_url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/","og_site_name":"SQL and Data Blog","article_modified_time":"2011-05-30T02:04:12+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/","url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/","name":"Help you to make better use of General SQL Parser","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2011-02-08T13:19:46+00:00","dateModified":"2011-05-30T02:04:12+00:00","description":"Parsing affected db objects(.NET version)","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/parsing-affected-db-objects-net-version\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"List of demos illustrate how to use general sql parser","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/"},{"@type":"ListItem","position":3,"name":"Parsing affected db objects(.NET version)"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/764"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=764"}],"version-history":[{"count":15,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/764\/revisions"}],"predecessor-version":[{"id":904,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/764\/revisions\/904"}],"up":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/235"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=764"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}