Search String in SPs excluding the comment section

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Search String in SPs excluding the comment section

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2012-04-20 : 09:44:57

Hi,

How can i get the list of stored procedures that uses the table 'EmployeeTable' excluding the stored procedures which contains the
'EmployeeTable' in comment section.

To find the list of stored procedures that contains the 'EmployeeTable'

I tried following, but my issue is it list the stored procedure name if 'EmployeeTable' contains in comment section also plz advice.

SELECT Name
FROM sys.objects
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

SELECT
DISTINCT o.name AS Object_Name,
o.type_desc ,
m.definition
FROM
sys.sql_modules m
INNER JOIN
sys.objects o
ON
m.object_id = o.object_id
WHERE
m.definition Like '%Employee%'
ORDER BY
2,1

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-20 : 10:03:18

might this help


Select Distinct
SysObjects.Name 'Table Name', 
Procedures.Name 'Stored Procedure'

From SysObjects 
Join (SysObjects Procedures 
Join SysDepends 
on Procedures.Id = SysDepends.Id) 
On SysDepends.DepId = SysObjects.Id

Where SysObjects.XType = 'U'

-- Change XType Values here using chart above
And Procedures.XType = 'P'

Group by SysObjects.Name, 
SysObjects.Id, 
Procedures.Name

Order by SysObjects.Name Asc

from http://www.myitforum.com/articles/1/view.asp?id=11086

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources