asked on

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.

Jim Horn

Give this a whirl..

exec sp_depends 'particular table'

Open in new window

MSDN article

Éric Moreau

give a try to the free http://www.red-gate.com/products/sql-development/sql-search/

dsacker

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.

ASKER CERTIFIED SOLUTION

Deepak Chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dsacker

I think I like sp_depends best, though, which Jim brought out. :)

Jyozeal

ASKER

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.

Deepak Chauhan

Did you try the Query which i shared you above to search in the DMV definition.

Jyozeal

ASKER

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.

Jyozeal

ASKER

deepakChauhan, yes I did and it worked. thank you.

Deepak Chauhan

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.

Jyozeal

ASKER

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.