Searching Stored Procedures for Table Names
-
I need to find all of the stored procedures in my database that access a particular table. It would also be interesting to know how to search them for any string.
Thank you,
Deborah
-
Select O.Name from dbo.SysObjects O inner join dbo.SysComments C ON o.id = C.ID and O.XType = 'P' AND OBJECTPROPERTY(O.id, 'IsMsShipped') = 0 AND C.Text like '%YourTableName%' ORDER BY O.Name
-
Thank you - perfect!
-
Ninja's query may not be accurate. If the text of a stored procedure is over 4000 characters, it is split across 2 (or more) sys_comments records. That split could occur in the middle of the table name, in which case the LIKE clause will never catch either the first or second row sys_comments row.
Another way is to use the sp_depends system stored procedure.
EXEC sp_depends @objname = N'YourTableName'
will return all references (function, sproces, views, constraints) that use that table. However, there is a caveat. The sysdepends table (that this sproc uses) is not always reliable. You can create a sproc that references a table that does not exist at the time, then build the table later. But in this case, the sysdepends will not have the dependency info for that sproc.
Both the query to syscomments and using sysdepends may not be accurate. To be better, do both methods to minimize your risk of missing a dependency.
Mark -
Thank you Mark! I will add this to my list of tools.
-
The sysdepends table (that this sproc uses) is not always reliableSysdepends is NEVER reliable. Let me put this this way : I have a db with about 1000 objects (450K of text >> 150+ pages of text in word). I'd guesstimate that when using sysdepends I get at least 40% of errors for missing objects. When using my query it goes down to much less than 1%. Why? because first, I have only 10-20 objects that use 2+ rows in sysdepends and second, what are the odds that I'll be using the tablename only once in the code of 4001+ characters which would happen to be splitted exactly in the middle and not be found anywhere else (not impossible but come on).
However I agree with Mark with the fact that my method is not perfect. That's why I wrote a routine that will concatenate the code of the SPS that spaw across more than 1 row. Then just run the search client side to finish the job (using my query on the server first for all the objects with only 1 row).
-
I've written code to do this - getting all syscomments text, that is - so if you'd like the code, PM me.
The code is a sproc that builds a table of objects and the corresponding syscomments text. This is then searchable via an Access front-end.
-
Code below gets around 4000 syscomments split problem.
declare @find varchar(1000)
set @find = ''-- enter search string here
set @sp_prefix = isnull(@sp_prefix,'') + '%'
set @find = '%' + @find + '%'select distinct o.name
from sysobjects o
join syscomments c1
on o.id = c1.id
left join syscomments c2
on c2.id = o.id
and c2.colid = c1.colid + 1
where o.xtype in ('P','TR')
and o.name like @sp_prefix
and c1.text + isnull(c2.text,'') like @find
-
Still not quite. You can have up to a few MBs of data for a single object. That's why I coded this client side in VBA where I have no string size limitattion. Select everything, concatenate the whole code, then do the search is the only bulletproof method I found.
-
haven't we done this one already in the last few weeks?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
4th or 5th time already in the last 2-3 months... This one comes up all the time.
-
No offence to Deborah, but I figured out how to do this almost at once with sql 6, and I'm sure several books have routines for this, surely most script libs must also contain this? Sometimes when I look at the posts I really wonder, doesn't anyone every check BOL, technet, msdn or even google ? Or even seach SQL central before posting ?
I'd also make the point that proper documentation would avoid having to do this task, but I'm an optimist !
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Agreed here. But I would think that the best possible documentation can hardly be as accurate and always up to date as the actual used code. That's why I have built a search algorithm on that
. -
I've come across this issue as well, but have just used this:
select routine_name
from information_schema.routines
where routine_definition like '%dbo.T_FOO%'
Is there any problem with doing it this way?
-
This is taken from the source code of the ROUTINE view :
ROUTINE_DEFINITION = convert(nvarchar(4000),
(SELECT TOP 1 CASE WHEN encrypted = 1 THEN NULL ELSE com.text END
FROM syscomments com WHERE com.id=o.id AND com.number<=1 AND com.colid = 1)),
EXTERNAL_NAME = convert(sysname,null),
So as you can see you get only the first row (com.colid = 1 where colid is the row id of the text of the object)... and not even a glimpse of a warning that there is more text to present.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply