{"id":1418,"date":"2012-06-14T10:48:37","date_gmt":"2012-06-14T15:48:37","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1418"},"modified":"2013-09-01T22:17:12","modified_gmt":"2013-09-02T03:17:12","slug":"sql-server-stored-procedure-analysis-tool","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/sql-server-stored-procedure-analysis-tool\/","title":{"rendered":"SQL Server stored procedure analysis tool"},"content":{"rendered":"

This tool can parse hundreds of T-SQL proc’s, generate output in a CVS file including following information:<\/p>\n

– Child SP’s called within a parent SP<\/p>\n

– Tables and columns selected in parent SP<\/p>\n

– Tables and columns updated in parent SP<\/p>\n

– Tables that are inserted data in parent SP<\/p>\n

After run tool like this:<\/p>\n

analyze_sp \/path_to_files\/*.sql \/o result.txt<\/p>\n

Let’s take this SQL for example:<\/p>\n

    \r\nUSE [Shop]\r\nGO\r\n\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nALTER proc [dbo].[pre_GetSomething]    \r\n @Top_XID int,      \r\n @KoXXXID int,  \r\n @dod_xrk nvarchar(50)   \r\nas       \r\n      \r\ndeclare @TempXXNo bigint, @SeXXID int , @Depo dt_Depo      \r\n      \r\nselect @TempXXNo=TempXXNo, @SeXXID=SeXXID, @Depo=Depo      \r\nfrom tb_xar_bas (nolock) where TopXXID=@Top_XID and KoXXXID=@KoXXXID       \r\n      \r\nif object_id('tempdb..#tmp_xxxly')>0      \r\n drop table #tmp_xxxly      \r\n      \r\nselect b.TempXXNo, b.SeXXID, db.From_DXXNo, db.To_DepX, d.Ur_xxID Ur_XXID1, r.Ur_XXID2, \r\nd.Mik_AS_XX Aso_a_bar, r.Mik_AS_XX Reseller_bar      \r\ninto #tmp_xxxly      \r\nfrom tb_xar_bas b (nolock)      \r\ninner join tb_Dept_xxBak db (nolock) on db.From_DXXNo=b.Depo and db.SeXXID=b.SeXXID      \r\ninner join tb_cargo_Del d (nolock) on b.TempXXNo=d.TempXXNo      \r\nleft join tb_Xun_Reduc r (nolock) on r.Ur_XXID1=d.Ur_xxID      \r\nwhere b.SeXXID=@SeXXID and b.Depo=@Depo      \r\n      \r\nupdate #tmp_xxxly set Ur_XXID2 = Ur_XXID1, Reseller_bar=1 where Ur_XXID2 is null      \r\n      \r\n      \r\nselect sum(Aso_a_bar*Reseller_bar) Kon_cXbtar       \r\nfrom #tmp_xxxly i (nolock)      \r\ninner join tb_Urun u (nolock) on u.Ur_xxID=i.Ur_XXID2      \r\nwhere TempXXNo=@TempXXNo and u.dod_xrk = @dod_xrk  \r\ngroup by TempXXNo, u.dod_xrk \r\n<\/pre>\nOutput generated:<\/p>\n\r\nDB of Anayzed Object|Name of Analyzed Object|Object Type|Object Used|Object Type|Usage Type|Columns\r\n[Shop]|[dbo].[pre_GetSomething]|SP|tb_xar_bas|Table|Read|TempXXNo,SeXXID,Depo,TopXXID,KoXXXID\r\n[Shop]|[dbo].[pre_GetSomething]|SP|#tmp_xxxly|Table|Drop|\r\n[Shop]|[dbo].[pre_GetSomething]|SP|tb_xar_bas|Table|Read|Depo,SeXXID,TempXXNo\r\n[Shop]|[dbo].[pre_GetSomething]|SP|tb_Dept_xxBak|Table|Read|From_DXXNo,SeXXID,To_DepX\r\n[Shop]|[dbo].[pre_GetSomething]|SP|tb_cargo_Del|Table|Read|TempXXNo,Ur_xxID,Mik_AS_XX\r\n[Shop]|[dbo].[pre_GetSomething]|SP|tb_Xun_Reduc|Table|Read|Ur_XXID1,Ur_XXID2,Mik_AS_XX\r\n[Shop]|[dbo].[pre_GetSomething]|SP|#tmp_xxxly|Table|Insert|\r\n[Shop]|[dbo].[pre_GetSomething]|SP|#tmp_xxxly|Table|Update|Ur_XXID2,Reseller_bar\r\n[Shop]|[dbo].[pre_GetSomething]|SP|#tmp_xxxly|Table|Read|Ur_XXID2,Aso_a_bar,Reseller_bar,TempXXNo\r\n[Shop]|[dbo].[pre_GetSomething]|SP|tb_Urun|Table|Read|Ur_xxID,dod_xrk\r\n\r\n<\/pre>\nDownload this tool C# version<\/a>, Java version<\/a>. <\/p>\n","protected":false},"excerpt":{"rendered":"This tool can parse hundreds of T-SQL proc’s, generate output in a CVS file including following information: – Child SP’s called within a parent SP – Tables and columns selected in parent SP – Tables and columns updated in parent SP – Tables that are inserted data in parent SP After run tool like this: analyze_sp \/path_to_files\/*.sql \/o result.txt Let’s take this SQL for example: USE [Shop] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[pre_GetSomething] @Top_XID int, @KoXXXID int, @dod_xrk nvarchar(50) as declare @TempXXNo bigint, @SeXXID int , @Depo dt_Depo select @TempXXNo=TempXXNo, @SeXXID=SeXXID, @Depo=Depo from tb_xar_bas\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