Check if a Table is used in any sp or function

Hello, there,

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?

THX

JohnQuery the syscomments table looking for the name of the table.

Q. John Chen wrote:

Quote:

Originally Posted by

Hello, there,
>
>
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?
>
THX
>
John

|||Q. John Chen (qjchen@.email.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Q. John Chen schreef:

Quote:

Originally Posted by

Hello, there,
>
>
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?
>
THX
>
John

Yep, there is:

CREATE proc [FindInObjects] (@.Search varchar(300))
as

SELECT so.xtype, so.name, sc.TEXT
FROM dbo.syscomments sc RIGHT OUTER JOIN
dbo.sysobjects so ON sc.id = so.id
WHERE so.xtype IN ('P', 'V', 'FN', 'TR') AND (sc.TEXT LIKE '%' +
@.Search + '%')

This will show you a list of all the objects that contain the search
string.

Enjoy,

GJ|||(gjvdkamp@.gmail.com) writes:

Quote:

Originally Posted by

CREATE proc [FindInObjects] (@.Search varchar(300))
as
>
SELECT so.xtype, so.name, sc.TEXT
FROM dbo.syscomments sc RIGHT OUTER JOIN
dbo.sysobjects so ON sc.id = so.id
WHERE so.xtype IN ('P', 'V', 'FN', 'TR') AND (sc.TEXT LIKE '%' +
@.Search + '%')
>
This will show you a list of all the objects that contain the search
string.

...unless the procedure name in question has been split up over a
chunk border. Keep in mind that the procedure text in syscomments is
split up in chunks of 4000 characters, and the split can well be in
the middle of an identifier.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx