Post reply

DML details from stored procedures

  • April 15, 2020 at 6:35 am

    #3743020

    Hi SSC,

    I'm wondering if the following is possible:

    I'm looking for a way to extract only DML Statements from all stored procedures in a DB.

    I know it's possible to get all the text from user objects that match a pattern, however I'm rather looking for a way to only get the next 100 chars from a match within the SQL text.

    Example: If I search for UPDATE I'd like to have as a resultset something like:

    User Object: dbo.sp_Insert

    Object Text: Update dbo.InsertTable where text is something that get's cut off.

    As some of these stored procedures might get past 4000 characters, ordering by the text would be nice to see for example which Inserts happen before some Updates are issued, I know it will not work on many occasions but at least I have a case of at least 1 procedure I could think of this might be useful for.

    What I'm trying to achieve is to get an understanding of some very large stored procedures I have to look into Performance wise.

    I have a rough understanding via TSQL script which procedure calls which other procedures  - actually now that I think about it, would be nice if triggers would be considered, too but that might be a different topic - and now I'd like to be able to add the DML targets so I get to know which tables are touched by which procedure.

    This is what I use to get the nested procedures

    SELECT *
    FROM (
        SELECT NAME AS ProcedureName
            ,SUBSTRING((
                    SELECT ', ' + OBJDEP.NAME
                    FROM sysdepends
                    INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
                    INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.
                        OBJECT_ID
                    WHERE obj.type = 'P'
                        AND Objdep.type = 'P'
                        AND sysdepends.id = procs.object_id
                    ORDER BY OBJ.NAME
                    FOR XML PATH('')
                    ), 2, 8000) AS NestedProcedures
        FROM sys.procedures procs
        ) InnerTab
    WHERE NestedProcedures IS NOT NULL
    ORDER BY ProcedureName

    any help to find related DML Statements would be highly appreciated.

  • April 16, 2020 at 7:10 am

    #3743318

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

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