Post reply

Which Stored Procedures Update Tables?

  • October 23, 2012 at 3:36 am

    #268993

    Hi,

    I'm using Sql Server 2008 and I was wondering if there was a clever way to work out which stored procedures within a database contain update/insert syntax relating to actual tables (not temp or table variables)?

    Thanks,

    Chris

    www.sqlAssociates.co.uk

  • October 23, 2012 at 4:25 am

    #1551814

    SELECT DISTINCT o.name, o.type_desc FROM sys.sql_modules m JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition LIKE '%view_cu_Account_Bills_b%'

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • October 23, 2012 at 4:41 am

    #1551821

    On SQL 2008, use the sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities. These track dependencies accurately, so you don't have to try and match to the object's definition. That said they show all dependencies, not just update, insert, delete.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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