Looking for a tool that can return SPs doing a DML on a table in SQL Server 2008

Question

I am working on a big application on SQL Server 2008 with 60 000 stored procedures and over 10 000 tables. Is there a tool through which I can do impact analysis to find out all SPs doing a DML on a table?

Answer 1

Try out SQL Search, by Red Gate (it's free).

Answer 2

The following SP will allow you to search for a Keyword (e.g. INSERT) and return all of the Objects where the Keyword exists within your database, giving:

• Object name
• Object type
• Useful command to see Object text

Due to technical difficulties with the Code block, I've had to split it up. But, create a new Stored Procedure, add 2 parameters:

@SearchStr varchar(100),
@RowsReturned int = NULL    OUT

And then paste in the following below:

    SELECT  DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
        CASE 
            WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1 
                THEN 'Replication stored procedure'
            WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 
                THEN 'Extended stored procedure'        
            WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1 
                THEN 'Stored Procedure' 
            WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1 
                THEN 'Trigger' 
            WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 
                THEN 'Table-valued function' 
            WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 
                THEN 'Scalar-valued function'
            WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 
                THEN 'Inline function'  
        END AS 'Object type',
        'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'
    FROM    syscomments c
        INNER JOIN
        sysobjects o
        ON c.id = o.id
    WHERE   c.text LIKE '%' + @SearchStr + '%'  AND
        encrypted = 0               AND
        (
        OBJECTPROPERTY(c.id, 'IsReplProc') = 1      OR
        OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1  OR
        OBJECTPROPERTY(c.id, 'IsProcedure') = 1     OR
        OBJECTPROPERTY(c.id, 'IsTrigger') = 1       OR
        OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
        OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1    OR
        OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1    
        )
    ORDER BY    'Object type', 'Object name'
    SET @RowsReturned = @@ROWCOUNT