SQL Server : how can I find the specific value from all stored procedures in my database?-CodePudding

How can I find the specific value from all stored procedures in my SQL Server database?

To be more specific, I want to know the value that is inserted into the specified column on the specified table.

For example:

A database has 3 stored procedures; dbo.sp_1, dbo.sp_2, dbo.sp_3
And that database also has a [Log] table which has a [number] column

dbo.sp_1

INSERT INTO [dbo].[Log] ([number]) 
VALUES (1);

dbo.sp_2

INSERT INTO [dbo].[Log] ([number]) 
VALUES (2);

dbo.sp_3

INSERT INTO [dbo].[Log] ([number]) 
VALUES (4);

So, the query results I expect are as follows:

CodePudding user response：

I found a snippet that was used for a somewhat-similar task, however, I did not have to parse values. This may get you close if you really have to parse the sql. Sorry, the rest of the parsing will be left up to you

DECLARE @NonEscapeTextToFindLike NVARCHAR(MAX) = 'INSERTINTO\[dbo\].\[LOG\](\[number\])VALUES('
DECLARE @NonEscapeTextToFind NVARCHAR(MAX) = 'INSERTINTO[dbo].[LOG]([number])VALUES('

;WITH Procs AS
(
    SELECT 
        StoredProcedure = name,
        StoredProcedureText = OBJECT_DEFINITION(object_id),
        NoBlankText = REPLACE(REPLACE(REPLACE(REPLACE(OBJECT_DEFINITION(object_id),' ',''),CHAR(9),''),CHAR(10),''),CHAR(13),'')
    FROM   
        sys.procedures
)

SELECT
    *,
    StartOfPossibleInt = CHARINDEX(@NonEscapeTextToFind, Procs.NoBlankText)   LEN(@NonEscapeTextToFind)
FROM
    Procs
WHERE
    Procs.NoBlankText LIKE '%' @NonEscapeTextToFindLike '%' ESCAPE '\'