How do I get a list of tables affected by a set of stored procedures?

Question

I have a huge database with some 100 tables and some 250 stored procedures. I want to know the list of tables affected by a subset of stored procedures. For example, i have a list of 50 stored procedures, out of 250, and i want to know the list of tables that will be affected by these 50 stored procedures. Is there any easy way for doing this, other than reading all the stored procedures and finding the list of tables manually?

PS: I am using SQL Server 2000 and SQL Server 2005 clients for this.

Answer 1

One very invasive option would be to get a duplicate database and set a trigger on every table that logs that something happened. Then run all the SP's. If you can't do lots of mods to the DB that wont work

Also, be sure to add the logging to existing triggers rather than replace them with logging if you also want tables that the SP's effect via triggers.

Answer 2

sp_depends 'StoredProcName' will return the object name and object type that the stored proc depends on.

EDIT: I like @KG's answer better. More flexible IMHO.

Answer 3

This would be your SQL Server query:

SELECT
    [NAME]
FROM
    sysobjects
WHERE
    xType = 'U' AND --specifies a user table object
    id in
    (
        SELECT 
            sd.depid 
        FROM 
            sysobjects so,
            sysdepends sd
        WHERE
            so.name = 'NameOfStoredProcedure' AND 
            sd.id = so.id
    )

Hope this helps someone.

Answer 4

I'd do it this way in SQL 2005 (uncomment the "AND" line if you only want it for a particular proc):

SELECT 
    [Proc] = SCHEMA_NAME(p.schema_id) + '.' + p.name,
    [Table] = SCHEMA_NAME(t.schema_id) + '.' + t.name,
    [Column] = c.name,
    d.is_selected,
    d.is_updated
FROM sys.procedures p
    INNER JOIN sys.sql_dependencies d
     ON d.object_id = p.object_id
     AND d.class IN (0,1)
    INNER JOIN sys.tables t
     ON t.object_id = d.referenced_major_id
    INNER JOIN sys.columns c
     ON c.object_id = t.object_id
     AND c.column_id = d.referenced_minor_id
WHERE   p.type IN ('P')
--  AND p.object_id = OBJECT_ID('MyProc')
ORDER BY 
    1, 2, 3