A stored proc to find objects in other stored procs
-
Does anyone know of a system stored proc that will check other stored pros for an object such as a table. You pass the object name and the proc gives you a result set of all the stored procs that the object is in. Say I'm looking a stored proc that uses table xyz. I want to find that proc that populates tables xyz.
Thanks
Charles
-
You could convert this to a sproc or use it as is...
- DECLARE @ObjectToFind VARCHAR(200)
SET @ObjectToFind = 'PutNameOfObjectToFindHere'
- SELECT DISTINCT
so.Name,so.XType
FROM dbo.SysObjects so,
dbo.SysComments sc
WHERE sc.Text LIKE '%' + @ObjectToFind + '%'
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - DECLARE @ObjectToFind VARCHAR(200)
-
you can use the following system stored procedure :
sp_depends 'objectname'
You will get all the objects which depend on that object.Hope it will be useful to u...
..hema
-
Great idea... a word of caution, though. I've found that sp_Depends will sometimes miss things... I believe that problem is also documented (somewhere) on this forum. Not sure if they fixed it in sp4 on SQL Server 2000 but they may have.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This isn't really what you asked for but it searchs for text and I use it to look for tables in SPs:
declare @s varchar(255)
set @s-2 = 'Text to Search Here'
DECLARE @msg varchar(255) ,
@ul varchar(255)
select 'SP Name'=upper(o.name),
Seq=colid ,
'SP Line'=substring(text,patindex(@s,text)-5, 30)
from syscomments c ,
sysobjects o
where o.id=c.id
and patindex(@s,text) > 0
order by name
SELECT @msg='* Stored procedures containing string "' + @s-2 + '=' +
convert(varchar(8),@@rowcount) + ' *'
SELECT @ul=replicate('*',datalength(@msg))
Print ' '
PRINT @ul
PRINT @msg
Print @ul
GO
-
/*
To my knowledge there is no System stored procedure to serve your purpose. However you can rely on sysdepends for dependant objects, if you have updated statistics.
This was a requirement in my work as well. So I use the following stored procedure to achieve the above
*/
----------------------------------------------------------
CREATE proc SyscommentText_ObjectName
@pObjName varchar(200)
as
begin
declare @vObjName varchar(210) ,
@vDynamicSQL nvarchar(255)
select @vObjName = '%' + @pObjName + '%'
select @vDynamicSQL = 'select distinct name from (
select object_name(id) name from syscomments
where text like ' + '''' + @vObjName + '''' + ' ) as a
order by name'
exec sp_executesql @vDynamicSQL
end
/* Run the above SP, with the table/sp as parameter as follows */
use pubs
go
execute SyscommentText_ObjectName 'authors'
Hope this helps
-
Note: Text can be split across rows in syscomments.
Here is a Stored Procedure from one of the first issues of SQL Server Professional, I think.
Provided "as is":
/* sp_grep v1.0 03/16/1995, v1.1 10/26/1995
** Author: Andrew Zanevsky, AZ Databases, Inc.
** Internet: 71232.3446@compuserve.com */
CREATE proc sp_grep @parameter varchar(255) = null, @case char(1) = 'i'
as
/* Note: @case parameter default changed from 's' to 'i'
to support default case-insensitive comparision,
as practiced at TRS.
*/
SET NOCOUNT ON -- PVP - Disable interim communication with caller.
SET QUOTED_IDENTIFIER OFF -- PVP - Added for v7 ANSI compatability.
declare @str_no tinyint,
@msg_str_no varchar(3),
@operation char(1),
@string varchar(80),
@oper_pos smallint,
@context varchar(255),
@i tinyint,
@longest tinyint,
@msg varchar(255)
if @parameter is null /* provide instructions */
begin
print 'Execute sp_grep "{string1}operation1{string2}operation2{string3}...", [case]'
print '- stringN is a string of characters up to 80 characters long, '
print ' enclosed in curly brackets. Brackets may be omitted if stringN '
print ' does not contain leading and trailing spaces or characters: +,-,&.'
print '- operationN is one of the characters: +,-,&. Interpreted as or,minus,and.'
print ' Operations are executed from left to right with no priorities.'
print '- case: specify "i" for case insensitive comparison.'
print 'E.g. sp_grep "alpha+{beta gamma}-{delta}&{+++}"'
print ' will search for all objects that have an occurence of string "alpha"'
print ' or string "beta gamma", do not have string "delta", '
print ' and have string "+++".'
return
end
/* Check for <CarriageReturn> or <LineFeed> characters */
if charindex(char(10), @parameter) > 0 or charindex(char(13), @parameter) > 0
begin
print 'Parameter string may not contain <CarriageReturn> or <LineFeed> characters.'
return
end
if lower(@case) = 'i'
select @parameter = lower(ltrim(rtrim(@parameter)))
else
select @parameter = ltrim(rtrim(@parameter))
create table #search (str_no tinyint, operation char(1), string varchar(80), last_obj int)
create table #found_objects (id int, str_no tinyint)
create table #result (id int)
/* Parse the parameter string */
select @str_no = 0
while datalength(@parameter) > 0
begin
/* Get operation */
select @str_no = @str_no + 1, @msg_str_no = rtrim(convert(char(3), @str_no + 1))
if @str_no = 1
select @operation = '+'
else
begin
if substring(@parameter, 1, 1) in ('+', '-', '&')
select @operation = substring(@parameter, 1, 1),
@parameter = ltrim(right(@parameter, datalength(@parameter) - 1))
else
begin
select @context = rtrim(substring(@parameter + space(255 - datalength(@parameter)), 1, 20))
select @msg = 'Incorrect or missing operation sign before "' + @context + '".'
print @msg
select @msg = 'Search string ' + @msg_str_no + '.'
print @msg
return
end
end
/* Get string */
if datalength(@parameter) = 0
begin
print 'Missing search string at the end of the parameter.'
select @msg = 'Search string ' + @msg_str_no + '.'
print @msg
return
end
if substring(@parameter, 1, 1) = '{'
begin
if charindex('}', @parameter) = 0
begin
select @context = rtrim(substring(@parameter + space(255 - datalength(@parameter)), 1, 200))
select @msg = 'Bracket not closed after "' + @context + '".'
print @msg
select @msg = 'Search string ' + @msg_str_no + '.'
print @msg
return
end
if charindex('}', @parameter) > 82
begin
select @context = rtrim(substring(@parameter + space(255 - datalength(@parameter)), 2, 20))
select @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
print @msg
select @msg = 'String begins with "' + @context + '".'
print @msg
return
end
select @string = substring(@parameter, 2, charindex('}', @parameter) - 2),
@parameter = ltrim(right(@parameter,
datalength(@parameter) - charindex('}', @parameter)))
end
else
begin
/* Find the first operation sign */
select @oper_pos = datalength(@parameter) + 1
if charindex('+', @parameter) between 1 and @oper_pos
select @oper_pos = charindex('+', @parameter)
if charindex('-', @parameter) between 1 and @oper_pos
select @oper_pos = charindex('-', @parameter)
if charindex('&', @parameter) between 1 and @oper_pos
select @oper_pos = charindex('&', @parameter)
if @oper_pos = 1
begin
select @context = rtrim(substring(@parameter + space(255 - datalength(@parameter)), 1, 20))
select @msg = 'Search string ' + @msg_str_no +
' is missing, before "' + @context + '".'
print @msg
return
end
if @oper_pos > 81
begin
select @context = rtrim(substring(@parameter + space(255 - datalength(@parameter)), 1, 20))
select @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
print @msg
select @msg = 'String begins with "' + @context + '".'
print @msg
return
end
select @string = substring(@parameter, 1, @oper_pos - 1),
@parameter = ltrim(right(@parameter,
datalength(@parameter) - @oper_pos + 1))
end
insert #search values (@str_no, @operation, @string, 0)
end
select @longest = max(datalength(string)) - 1
from #search
/* ------------------------------------------------------------------ */
/* Search for strings */
if @case = 'i'
begin
insert #found_objects
select a.id, c.str_no
from syscomments a, #search c
where charindex(c.string, lower(a.text)) > 0
insert #found_objects
select a.id, c.str_no
from syscomments a, syscomments b, #search c
where a.id = b.id
and a.number = b.number
and a.colid + 1 = b.colid
and charindex(c.string,
lower(right(a.text, @longest) +
/* space(255 - datalength(a.text)) +*/
substring(b.text, 1, @longest))) > 0
end
else
begin
insert #found_objects
select a.id, c.str_no
from syscomments a, #search c
where charindex(c.string, a.text) > 0
insert #found_objects
select a.id, c.str_no
from syscomments a, syscomments b, #search c
where a.id = b.id
and a.number = b.number
and a.colid + 1 = b.colid
and charindex(c.string,
right(a.text, @longest) +
/* space(255 - datalength(a.text)) +*/
substring(b.text, 1, @longest)) > 0
end
/* ------------------------------------------------------------------ */
select distinct str_no, id into #dist_objects from #found_objects
create unique clustered index obj on #dist_objects (str_no, id)
/* Apply one operation at a time */
select @i = 0
while @i < @str_no
begin
select @i = @i + 1
select @operation = operation from #search where str_no = @i
if @operation = '+'
insert #result
select id
from #dist_objects
where str_no = @i
else if @operation = '-'
delete #result
from #result a, #dist_objects b
where b.str_no = @i
and a.id = b.id
else if @operation = '&'
delete #result
where not exists
(select 1
from #dist_objects b
where b.str_no = @i
and b.id = #result.id)
end
/* Select results */
select distinct id into #dist_result from #result
/* The following select has been borrowed from the sp_help
** system stored procedure, and modified. */
SET NOCOUNT OFF -- PVP Display RowsAffected.
select DISTINCT -- PVP Trim displayed list.
Name = o.name,
/* Remove 'convert(char(15)' in the following line
** if user names on your server are longer. */
Owner = convert(char(15), user_name(uid)),
Object_type = substring(v.name + x.name, 1, 16)
from #dist_result d,
sysobjects o,
master.dbo.spt_values v,
master.dbo.spt_values x
where d.id = o.id
/* SQL Server version 6.x uses 15, prior versions use 7 in expression below */
and o.sysstat & (7 + 8 * sign(charindex('6.', @@version))) = v.number
and v.type = 'O'
and x.type = 'R'
and o.userstat & -32768 = x.number
order by Object_type , Name
GO
-
Jeff... Thanks, the above code is what I'm looking for. I added to the where clause to check for the type of object I'm looking for (where type = 'P').
Thanks
-
List any Object In Any Database On Server:
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1620
-
You bet, Charles. Thank you for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply