in SQL Server 2008 how can I get the list of stored procedures which are inserting data in a given table?

I need to add a field in a table. This field will need to be populated with different values.

I already know the main stored procedures which insert n开发者_开发技巧ew records in this table and those stored procedures will need to be modified. To stay on the safe side is there a way I can get the full list of stored procedures which insert records in the table in question?

sys.dm_sql_referencing_entities gives me the list of stored procedures which are dependant on this table but I only want the stored procedures which insert records not the ones which simply query it.

Thanks.

You could try to look on the code of the stored procedure itself, but it probably won't be a 100 percent match. Something on the lines of:

SELECT OBJECT_NAME(S.object_id) StoredProcedure
FROM sys.sql_modules S
JOIN sys.procedures P
ON S.object_id = P.object_id
WHERE Definition LIKE '%INSERT INTO myTable%' 
OR Definition LIKE '%INSERT myTable%' 

I dont know that we have a way to get a hard match based on DML statement (insert vs delete), but you might try comparing the referencing entities dataset against a string match like so:

declare @ObjectName nvarchar(517) = 'dbo.Person';

declare @match table (sName nvarchar(500), oName nvarchar(500))
insert into @Match (sName, oName)
    select distinct
            object_schema_name([object_id], db_id()),
            object_name([object_id], db_id())
    from    sys.sql_modules
    where   [definition] like '%'+@ObjectName+'%' and
            (   -- match "insert into dbo.Person" or "insert into Person"
                [definition] like '%insert into ' + @ObjectName + '%' or
                [definition] like '%insert into ' + replace(@ObjectName, 'dbo.', '') + '%' or
                -- match "insert dbo.Person" or "insert Person"
                [definition] like '%insert ' + @ObjectName + '%' or
                [definition] like '%insert ' + replace(@ObjectName, 'dbo.', '') + '%' 
            )

select  *   
from    sys.dm_sql_referencing_entities(@ObjectName, 'OBJECT') re
left
join    @Match d on
        re.referencing_schema_name = d.sName and
        re.referencing_entity_name = d.oName
where   d.sName is not null -- comment this line to compare all possible ref entities 
return

One low-tech tactic that works better than you might think . . .

Dump the schema to text. Then at a command prompt . . .

$ grep -i 'insert into your-table-name' dumped-schema-file

I don't know the Windows utility that searches for a text string.

That will probably find most of the inserts. You might also try

$ grep -i -B2 'your-table-name' dumped-schema-file

That will print every line that matches 'your-table-name', along with the three lines immediately before it, so you can look for things like

insert into
your-table-name

SQL Server Magazine published an article titled Stored Procedure Searches for Strings a few years ago that provided a stored procedure that

accepts an input string and searches the syscomments, sysobjects, and syscolumns system tables in the local database for any reference to that the string. The output result set shows all the tables, procedures, views, and other elements that contain the specified input string.

It's basically a grep for your database. It's been very useful.