SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Search String in SPs excluding the comment section		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vision.v1
Yak Posting Veteran

65 Posts
Posted - 04/20/2012 :  09:44:57  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

USA
1608 Posts
Posted - 04/20/2012 :  10:03:18  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000