SQL Server stored procedure analysis tool

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 (nolock) where TopXXID=@Top_XID and KoXXXID=@KoXXXID       
      
if object_id('tempdb..#tmp_xxxly')>0      
 drop table #tmp_xxxly      
      
select b.TempXXNo, b.SeXXID, db.From_DXXNo, db.To_DepX, d.Ur_xxID Ur_XXID1, r.Ur_XXID2, 
d.Mik_AS_XX Aso_a_bar, r.Mik_AS_XX Reseller_bar      
into #tmp_xxxly      
from tb_xar_bas b (nolock)      
inner join tb_Dept_xxBak db (nolock) on db.From_DXXNo=b.Depo and db.SeXXID=b.SeXXID      
inner join tb_cargo_Del d (nolock) on b.TempXXNo=d.TempXXNo      
left join tb_Xun_Reduc r (nolock) on r.Ur_XXID1=d.Ur_xxID      
where b.SeXXID=@SeXXID and b.Depo=@Depo      
      
update #tmp_xxxly set Ur_XXID2 = Ur_XXID1, Reseller_bar=1 where Ur_XXID2 is null      
      
      
select sum(Aso_a_bar*Reseller_bar) Kon_cXbtar       
from #tmp_xxxly i (nolock)      
inner join tb_Urun u (nolock) on u.Ur_xxID=i.Ur_XXID2      
where TempXXNo=@TempXXNo and u.dod_xrk = @dod_xrk  
group by TempXXNo, u.dod_xrk 

Output generated:

DB of Anayzed Object|Name of Analyzed Object|Object Type|Object Used|Object Type|Usage Type|Columns
[Shop]|[dbo].[pre_GetSomething]|SP|tb_xar_bas|Table|Read|TempXXNo,SeXXID,Depo,TopXXID,KoXXXID
[Shop]|[dbo].[pre_GetSomething]|SP|#tmp_xxxly|Table|Drop|
[Shop]|[dbo].[pre_GetSomething]|SP|tb_xar_bas|Table|Read|Depo,SeXXID,TempXXNo
[Shop]|[dbo].[pre_GetSomething]|SP|tb_Dept_xxBak|Table|Read|From_DXXNo,SeXXID,To_DepX
[Shop]|[dbo].[pre_GetSomething]|SP|tb_cargo_Del|Table|Read|TempXXNo,Ur_xxID,Mik_AS_XX
[Shop]|[dbo].[pre_GetSomething]|SP|tb_Xun_Reduc|Table|Read|Ur_XXID1,Ur_XXID2,Mik_AS_XX
[Shop]|[dbo].[pre_GetSomething]|SP|#tmp_xxxly|Table|Insert|
[Shop]|[dbo].[pre_GetSomething]|SP|#tmp_xxxly|Table|Update|Ur_XXID2,Reseller_bar
[Shop]|[dbo].[pre_GetSomething]|SP|#tmp_xxxly|Table|Read|Ur_XXID2,Aso_a_bar,Reseller_bar,TempXXNo
[Shop]|[dbo].[pre_GetSomething]|SP|tb_Urun|Table|Read|Ur_xxID,dod_xrk

Download this tool C# version, Java version.