How to search for Table usage in a Stored Proc

Question

Post reply

How to search for Table usage in a Stored Proc

kelvin pryce

SSC Enthusiast

Points: 143
More actions
May 12, 2005 at 8:53 am

#164437

This quesion is bugging me, and I know its going to be something so simple Im almost tempted to not ask. However, all manuals and books onlinew have been of no use whatsoever in answering this question so;
Is there a way of finding out which of my stored procedures use a particular table, without manually having to look through them all?

Viewing 5 posts - 1 through 4 (of 4 total)

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

use this sql (pulled from this site)

SET NOCOUNT ON

SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',

CASE

WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1

THEN 'Replication stored procedure'

WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1

THEN 'Extended stored procedure'

WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1

THEN 'Stored Procedure'

WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1

THEN 'Trigger'

WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1

THEN 'Table-valued function'

WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1

THEN 'Scalar-valued function'

WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1

THEN 'Inline function'

END AS 'Object type',

'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'

FROM syscomments c

INNER JOIN

sysobjects o

ON c.id = o.id

-- CHANGE THE LIKE CLAUSE TO THE STRING PORTION YOU ARE SEARCHING FOR

WHERE c.text LIKE '%Zanevsky%' AND

encrypted = 0 AND

(

OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR

OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR

OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR

OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR

OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR

OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR

OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1

&nbsp

ORDER BY 'Object type', 'Object name'

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

You could use sysdepends but it is notoriously unreliable.. so this would be a start...

Select DISTINCT O.name from dbo.SysComments C inner join dbo.SysObjects O on C.id = O.id and O.XType = 'P' where text like '%TableName%'

But you'd still have to manually check every procs returned manually to confirm or infirm the usage of the said table.

ron k SSCertifiable Points: 6103 More actions · Answer 3

Also from this site:

ALTER procedure sp_FindText @SearchText varchar(100)

AS

/*

Acknowledgement: Okie_Greg

Source: http://www.sqlservercentral.com/forums

Location: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=102649&p=2

Date: 21-Apr-04

*/

SELECT sysobjects.name AS 'Stored Procedure'

FROM sysobjects,syscomments

WHERE sysobjects.id = syscomments.id

AND

sysobjects.type = 'P'

AND

sysobjects.category=0

AND

charindex(@SearchText,syscomments.text)>0

RETURN

kelvin pryce SSC Enthusiast Points: 143 More actions · Answer 4

Truly impressed with the speed of your replies gentlemen - especially as I posted my mail before Id written 'please' or 'thankyou'.

Many thanks to andrewkane17, Remi Gregoire and ron k.

All fundamentally similar answers, using syscomments and sysobjects, problem solved!

Cheers, Kelvin.