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
niceguy971Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
niceguy971Author Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.