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

# 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.