January 2, 2007 at 12:43 pm
Is there a way to list User Stored Procedures that reference a specified table?
January 2, 2007 at 12:52 pm
Try sp_depends 'table name'.
This system sp queries sysdepends (system table).
January 2, 2007 at 12:57 pm
Thanks a lot!!! That worked great!!!
January 3, 2007 at 5:08 am
Be careful though. Sysdepends is not guaranteed to be accurate at all times. The most thorough way is to grep through syscomments with a LIKE clause of '%tablename%' and join that output to sysobjects to get the sproc name. This can have issues also since columns can wrap in the middle of the table name so you need to concatenate them together for each sproc and then check it to be 100% certain you have found all sprocs correctly.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 3, 2007 at 5:46 am
As a follow-up to Kevin's post, this is something I did recently to provide a search facility where I work.
I've got a sproc that creates a table containing all the relevant syscomments/sysobjects info, which is then searchable. I can pass it on if anyone is interested; PM me if so.
January 3, 2007 at 5:54 am
not difficult - I usually drop this proc into master - can use it in from any database, will search for one or two strings
Create procedure dbo.sp_FindText
@wot varchar(250)=' ',
@wot2 varchar(250)=' '
-- ============================================================================
-- Stored Procedure: sp_FindText
--
-- Written by: Colin Leversuch-Roberts
-- kelem consulting limited
-- http://www.kelemconsulting.co.uk
-- (c) august 2004
--
-- Purpose: Search for system objects containing the passed string(s)
-- These are wild card searches
-- Returns the object code and type of object
--
-- System: master database
-- does not need to be marked as a system object
--
-- Input Paramters: @wot varchar Search string
-- @wot2 varchar Search string
--
-- Output Parameters: None
--
-- Usage: Call from user database to be searched
-- EXEC dbo.sp_findtext 'container'
-- EXEC dbo.sp_findtext 'rates','uat'
--
-- Calls: nothing
-- Uses: syscomments, sysobjects
--
-- Data Modifications: None
--
-- VERSION HISTORY
-- Version No Date Description
-- 1 22-August-2004 Initial Release
-- ============================================================================
as
set nocount on
--
select obj.name,obj.xtype,
case obj.xtype
when 'TR' then 'Trigger'
when 'P' then 'Procedure'
when 'V' then 'View'
when 'TF' then 'Function'
when 'IF' then 'Function'
when 'FN' then 'Function'
else 'Unknown'
end
,c.text
from dbo.syscomments c join dbo.sysobjects obj
on obj.id=c.id
where
patindex(<A href="mailto:'%'+@wot+'%',text)0">'%'+@wot+'%',text)<>0 and patindex(<A href="mailto:'%'+@wot2+'%',text)0">'%'+@wot2+'%',text)<>0
GO
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 3, 2007 at 8:50 am
Colin, your query does exactly what I cautioned against. Suppose a line of a sproc wraps in syscomments (nvarchar 8000 IIRC), and it wraps in such a way to split a table name. Example:
select field1, field2 from tblmys
plittable where a = @a
Now your query will NOT identify that sproc as referencing tblmysplittable. You really do need to concatenate the rows together (or at least each row to the next row) to guarantee finding all words correctly.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 3, 2007 at 2:58 pm
Well, if you're running SQL Server 2005, you can query sys.sql_modules instead. Something like the code below works well, because sys.sql_modules is varchar(max).
-- This query returns the Name of the object, and it's entire definition
select syso.[name], sysm.definition
from sys.sysobjects syso
join sys.sql_modules sysm on syso.id = sysm.[object_id]
where sysm.definition like '%Table_Name%'
and syso.uid = 1 -- only get objects owned by DBO (avoid system objects)
Rick
townsends.ca
January 3, 2007 at 3:00 pm
It's a simple script, never had any problems, but that's probably down to documentation and I usually try to avoid large procs as they rarely optimise correctly in cache. If I needed a more complex script I'd write one, I forget how many years I've used this script, at least since 7.0 and probably in 6.0 - yup I do agree about splits - you can always profile of course, or script out all your procs and search the resulting script file
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 3, 2007 at 4:02 pm
Yeah, the likelyhood of specific search string spanning multiple blocks isn't high, but it can happen. Syscomments.text is nvarchar(4000), so it's easy to span multiple segments. We use Nice-Large-Comment-Blocks at the start of all database objects, and include history as the object changes. It's easy to pass 4000 characters that way. Check your objects, I expect you'll get more results than you thought:
select id, count(*) from sys.syscomments group by id having count(*) > 1
Dragged from the recesses of my time-addled brain... Here's a search script for SQL Server 2000 that will find any table reference, even spanning across multiple chunks. Its only limitation is the 8000 char variable limit, so you're limited to searching a max of (8000 / (len(@sText) * 2)) chunks. (ie: if you're searching for a string that's 500 characters long, you can't search past the 8th chunk, or 32,000th character.)
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
create table #results (objid bigint, [type] sysname, obj sysname, comment varchar(8000))
declare @obj sysname,
@type sysname,
@objid bigint,
@sText varchar(1027),
@wrapText varchar(8000)
-- Search for the following string
set @sText = 'DeliveryDrop_Type'
-- Look in syscomments for the search string
insert into #results
select syso.[id] as objid, substring(val.name, 5, 16) as [type], syso.[name] as [obj], [text]
from syscomments sysc
join sysobjects syso on sysc.[id] = syso.[id]
join master.dbo.spt_values val on syso.xtype = substring(val.[name],1,2)
where syso.[type] in ('FN', 'IF', 'P', 'RF', 'TF', 'TR', 'V')
and [text] like '%' + @sText + '%'
-- Collect the details of any objects that haven't yet matched the search string
select syso.[id] as objid, syso.[name], substring(val.name, 5, 16) as [type]
into #objects
from sysobjects syso
join master.dbo.spt_values val on syso.xtype = substring(val.[name],1,2)
left join #results r on syso.[id] = r.objid
where r.objid is null
and syso.[type] in ('FN', 'IF', 'P', 'RF', 'TF', 'TR', 'V')
while exists (select top 1 * from #objects)
begin
select top 1
@obj = [name],
@type = [type],
@objid = [objid],
@wrapText = ''
from #objects
select @wrapText = isNull(@wrapText, '') + left(text, len(@sText) - 1) + '~' + right(text, len(@sText) - 1)
from sys.syscomments
where id = @objid
if (CHARINDEX(@sText, @wrapText) > 0)
insert into #results
select @objid, @type, @obj, @wrapText
delete from #objects
where [objid] = @objid
end
select *
from #results
order by [type], [obj], [comment]
drop table #objects
drop table #results
SET ANSI_WARNINGS ON
SET NOCOUNT OFF
Rick
townsends.ca
January 4, 2007 at 6:26 am
ive used this in the past; it's a cursor, and resource intensive; it takes a couple of minutes to run on my 100meg database with 100's of procs and 1400+tables:
sample results:
| ProcName | MightReferenceObject |
| PR_ACTDELFAVORITES | GMACT |
| PR_ACTDELFAVORITES | GMACTFAVORITES |
| PR_COMPLETEDELETE_ACT | GMACTFAVORITES |
| PR_COMPLETEDELETE_ACT | GMHOPWA6 |
| PR_COMPLETEDELETE_ACT | GMHOPWA7 |
--search all procedures and find out if the name of any of the objects appear in their text:
SET NOCOUNT ON
declare
@isql varchar(2000),
@objname varchar(64)
--All User Tables, Views, Procedures, Table function or Scalar Function.
Create Table #MightReference(
ProcName varchar(60),
MightReferenceObject varchar(60)
)
declare c1 cursor for select name from sysobjects where xtype in ('U','V','P','TF','FN')
open c1
fetch next from c1 into @objname
While @@fetch_status <> -1
begin
--desired logic: if an object name exists in the syscomments of a procedure,
--it MIGHT mean the procedure references the object
--insert the two into a table for further evaulation.
Insert into #MightReference(ProcName,MightReferenceObject)
select object_name(id),@objname from syscomments
where id in(select id from sysobjects where xtype='P' and name NOT like 'dt_%')
and text like '%' + @objname + '%'
fetch next from c1 into @objname
end
close c1
deallocate c1
--avoid self referencing procs. "CREATE PROC MYPROC" statement returns itself!
Select * from #MightReference
where ProcName <> MightReferenceObject
order by ProcName
Select * from #MightReference
where ProcName <> MightReferenceObject
order by MightReferenceObject
Lowell
January 18, 2007 at 2:32 pm
Rick,
Thanks for the script.
However, when I ran it, I got the following errors:
Warning: The table '#results' has been created but its maximum row size (8547) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table '#results' has been created but its maximum row size (8547) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Server: Msg 8648, Level 17, State 95, Line 16
Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint.
Warning: The table '#results' has been created but its maximum row size (8547) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
any ideas?
Thank you
January 18, 2007 at 2:51 pm
Lowell,
Does your script solve the problem of stored procedure text spanning over multiple lines?
If so, could you please point to the part of the code that takes care of that.
Thank you
January 18, 2007 at 3:47 pm
sql_er no it does not address the issue where a tablename might span across the 4000 char mark in syscomments;
in my case, i check sysdepends and this, and it's "good enough for government work", but not foolproof. I only have a few procs or functions that are big enough to span multi rows, so in my case it's not enough to worry about.
Sergy has posted some code on this same subject that does consider multi rows, so if you need something bullet proof, search the forums here; i've seen it several times, but couldn't find it in a search for this post.
Lowell
January 19, 2007 at 9:06 am
Here's a simple script we use that does two joins to syscomments to search stored procedure text that spans two rows:
Greg
select distinct so.name from sysobjects so
join syscomments sc on sc.id = so.id
left outer join syscomments sc1 on sc1.id = sc.id and sc1.colid = sc.colid + 1
where so.type = 'p'
and ( sc.text like '%string%'
or right(sc.text,500) + left(isnull(sc1.text,''),500) like '%string%'
)
order by so.name
Greg
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply