Posted by Erland Sommarskog on 07/13/06 21:49

Q. John Chen (qjchen@email.com) writes:
> I am trying to find out if a table is used by any of the stored
> procedures or functions.
>
> I can generate all the scripts and look for it. But is there an easy
> way?

sp_depends.

However, it's not reliable, since dependencies are lost if the table
is dropped and recreated. Or the proc/function was created before the
table was.

syscomments that Stu mentions is neither that safe, as text here is
sliced into chunks of 4000 chars, and the table name could occur
on a chunk border.

So scripting is the only way. Or searching the version-control system.
Because you do keep all your code under version control. don't you?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: SQL Server 2005 Startup error - TDSSNIClient... error 0x5... code 0x51
• Prev in forum: Re: Input string -> table -> output string?
• Thread view: Re: Check if a Table is used in any sp or function

[Reply to this message]