Search for table name in all stored procedures' text in given DB

Hi All,

I would like to find out if there is a way of finding out which stored procedures and UDFs are using particular table. Can we use DMVs to achieve this? if yes can you tell me how?

The reason for asking is I need to add 3 columns to one of the tables which is almost heart of the application. So I would like to see if in any stored procedure select * is used from this table instead of listing out all columns(we have select * as bad coding practice so I may not find any SPs but still I need to check). Going thru all related SPs seems daunting because I have so many of them.

Thanks for your time.
JyozealAsked:
Who is Participating?
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..
exec sp_depends 'particular table'

Open in new window


MSDN article
0
Éric MoreauSenior .Net ConsultantCommented:
0
dsackerContract ERP Admin/ConsultantCommented:
You can use the information_schema.routines view to sift through routine (procs and functions) definition code:

SELECT  *
FROM    information_schema.routines
WHERE   routine_definition LIKE '%YourTableName%'

Open in new window

That will not immediately tell you what that proc is doing to the table (if anything), but it will list those that reference the table.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Deepak ChauhanSQL Server DBACommented:
You can query to sys.all_sql_modules if it is in SP , Function or View

select object_name(object_id) as OBJName, definition from sys.all_sql_modules
where definition like '%TableName%'
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
dsackerContract ERP Admin/ConsultantCommented:
I think I like sp_depends best, though, which Jim brought out. :)
0
JyozealAuthor Commented:
Thanks all for your quick reply. sp_depends is what I need I think however that is returning 200 rows. so can I search for 'select *' in the definition?

Éric Moreau, I cannot install it because of enterprise restrictions. I really liked that one and will use for my personal projects.

dsacker, I am getting some procs where my table is not used. So I am still looking into the query you gave and will get back to you for any change I see.
0
Deepak ChauhanSQL Server DBACommented:
Did you try the Query which i shared you above to search in the DMV definition.
0
JyozealAuthor Commented:
I tried the below and it seems to work

select object_name(object_id) as OBJName, definition
from sys.all_sql_modules
where objectproperty(object_id,'IsMsShipped') = 0 and definition like '%select * from mytablename%'

https://msdn.microsoft.com/en-us/library/ms188757.aspx

ROUTINE_DEFINITION
 
nvarchar( 4000)
 
Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL.

To ensure that you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.
0
JyozealAuthor Commented:
deepakChauhan, yes I did and it worked. thank you.
0
Deepak ChauhanSQL Server DBACommented:
ok. You are searching on basis of string "%select * from tablename%" but what if  definition is like "select column from tablename" in this case you will not get the correct result.
0
JyozealAuthor Commented:
right. I am going to add few more columns to my table. my change will not break the application  if column names are specified in the query. Only if select * is used my code is going to break the application. So I am searching for that string. Let me know if I am missing anything.
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 2005

From novice to tech pro — start learning today.

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.