• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

Sql Server 2008--How to identify all stored procedures referencing a particular table MyTableCustomer

What would be the best way ( easiest way ) to  identify all stored procedures referencing a particular table MyTableCustomer

in Sql Server 2008?

Thanks
0
niceguy971
Asked:
niceguy971
3 Solutions
 
Tapan PattanaikSenior EngineerCommented:
Hi niceguy971,

Please check the below query

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


OR

Locate the table(MyTableCustomer), right click and choose "View dependencies".

Regards,
Tapan Pattanaik
0
 
Pratima PharandeCommented:
----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

refer
http://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure/
0
 
niceguy971Author Commented:
Hi Taran and Pratima,

Scenario: SP belongs to Database MyDatabaseOne--> this SP uses table from ANOTHER Database

MyDatabaseTHREE.dbo.MyTableCustomer.

How do you handle this scenario??

Looks like your approach will Not work.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I once had similar need and developed the following script:
DECLARE @MySelect VARCHAR(MAX)

SET @MySelect='USE ? ;
				DECLARE @ObjectName VARCHAR(MAX)
				DECLARE @ObjectType CHAR(1)

				DECLARE SP_VIEW CURSOR FOR
					SELECT DB_NAME() + ''.'' + SCHEMA_NAME(o.schema_id) + ''.'' + o.name ObjName, o.type
					FROM sys.objects o
					WHERE o.object_id > 255 AND o.type IN (''P'', ''V'')

				OPEN SP_VIEW 
				FETCH NEXT FROM SP_VIEW 
				INTO @ObjectName, @ObjectType

				WHILE @@FETCH_STATUS = 0
					BEGIN
						CREATE TABLE #Result (Text varchar(MAX))
						INSERT #Result exec sp_helptext @ObjectName

						IF EXISTS (SELECT 1 FROM #Result WHERE Text LIKE ''%MyTableCustomer%'')
							PRINT @ObjectType + ''-->'' + @ObjectName

						DROP TABLE #Result
						
						FETCH NEXT FROM SP_VIEW 
						INTO @ObjectName, @ObjectType 
					END

				CLOSE SP_VIEW 
				DEALLOCATE SP_VIEW'

EXEC sp_MSforeachdb @MySelect

Open in new window

NOTE: You can see inside the text string that exists a LIKE, where I replaced the value for %MyTableCustomer% but you can replace it to any value that you want to be found.
0
 
niceguy971Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now