> Hello,
>
> I understand that you'd like to know when SQL creates a dependency of a SP.
>
> As I know, a dependency is created when the procedure is created unless
> Deferred Name Resolution is used. Also, a depndency is created only when
> the object such as table exists before the SP is created.
>
> Deferred Name Resolution and Compilation
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm
> _8_des_07_5wa6.asp
>
> The dependencies stored by SQL Server are not always reliable. The better
> option might be querying the syscomments table if you want to check if a
> table is referenced by any SP:
>
> SELECT DISTINCT o.name
>     FROM dbo.syscomments c
>     JOIN dbo.sysobjects o
>         ON c.id = o.id
>     WHERE o.xtype = 'P'
>     AND c.text LIKE '%tbl_example%';
>
> If you want to find other tables related to this one,  for example, if you
> are looking more for if table A has column B, that is a foreign key to
> table C, so table C would have a dependency on table A, you may need to run
> sp_fkeys SP.
>
> If anything is unclear, or you have further questions, please feel free to
> let me know. We look forward to your reply.
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

> Hello,
>
> My pleasure. If you need any further help, please feel free to post back.
> :-)
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
>
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>
>

> Hello,
>
> Thank you for your reply. The sysobjects table in the query is used to
> search for the objects such as SPs referenced by table1. Therefore, it is
> not appropriate to add o.name=table1 in the query. The following query
> works on my side.
>
>
> SELECT DISTINCT o.name
> FROM dbo.syscomments c
> JOIN dbo.sysobjects o
> ON c.id = o.id
> WHERE o.xtype = 'P'
> AND c.text LIKE '%table1%'
>
> The result are all Stored procedures that reference the table1 in their
> script.
>
> If you still have concerns or questions, please do not hesitate to let's
> know. Thank you.
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
>
> =====================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>
>