Post reply

Script entire db to find relevant Stored procedures

  • August 3, 2022 at 10:58 am

    #4071832

    I have scripted my entire database to find the relevant stored procedures(related to case #4071749 - https://www.sqlservercentral.com/forums/topic/m:issing-datafinding-the-root-problem#post-4071649)

    My function return zero values for specific columns, see screenshots:getsummery_0f5

    Table "auditLevyBreakdown" returns zero data. I would like to find the relevant stored procedure to find the root cause. Please advise?

    List of my procedures:

     

    • This topic was modified 2 years, 4 months ago by  yrstruly.
    • This topic was modified 2 years, 4 months ago by  yrstruly.

    Attachments:
    You must be logged in to view attached files.

  • August 3, 2022 at 3:19 pm

    #4071997

    Are you trying to identify the stored procedure that should insert data into your empty table "auditLevyBreakdown"?

    Does this return anything? I see the procedure names start with sp, is there any chance they were compiled in the master database?

    SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%auditLevyBreakdown%'

    Do the procedures load data using dynamic sql? Is it possible that "auditLevyBreakdown" is a value in a Table Name column that is used to generate dynamic sql to load the table?

  • August 3, 2022 at 4:03 pm

    #4072036

    Yes, i am trying to find out which stored procedure or table. I was just given this task and thrown in the deep end. I am still learning the context of the data.

    Attachments:
    You must be logged in to view attached files.

  • August 3, 2022 at 4:06 pm

    #4072039

    yrstruly wrote:

    Yes, i am trying to find out which stored procedure or table. I was just given this task and thrown in the deep end. I am still learning the context of the data.

    Please see my reply on the thread that spawned this need at

    https://www.sqlservercentral.com/forums/topic/missing-datafinding-the-root-problem/page/2#post-4072000

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply