Get sp names who is using given table name
-
Hi gurus,
I need to get the list of stored procs which is using a one table name
ex: ListofSP('Table1') - should listed the sp's who is using Table1
Thankx in advance
My Blog:
http://dineshasanka.spaces.live.com/ -
Since all sourcecode can be found in syscomments you might want to try to query this table. Have you searched the script section here already? It's likely that someone else did this before.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Something like this?
(as a bonus this also lists which type of object 'word' is found in)
-- a quick & dirty way of searching through syscomments for a 'word'
-- returning the name and type of objects where the word is present
set nocount on
create table #list ( objectname varchar(35) not null, searchword varchar(25) not null, type char(2) not null)
create table #x ( searchword varchar(25) not null )
insert #x select 'order' -- <=== enter searchword here
declare @s-2 varchar(45)
declare a cursor for select searchword from #x
open a
fetch a into @s-2
while @@fetch_Status = 0
begin
insert #list ( objectname, searchword, type)
select distinct object_name(o.id), @s-2, o.type
from sysobjects o
join syscomments c
on o.id = c.id
and c.text like '%' + @s-2 + '%'
fetch a into @s-2
end
close a
deallocate a
select objectname, searchword, type
from #list
order by objectname, searchword
drop table #x
drop table #list
set nocount off
go
.. apologies for being bad and going on systemtables directly, but this was something old I found
/Kenneth
-
There is a free product called Toad.exe that can find that for you and has many other uses. Download it from http://www.toadsoft.com.
Once you have it and connect to the database go to the data browser (first icon in the upper left of the screen). Click on the table, then click on Deps (used by). It will list all the views and stored procedures using the table.
-
I wonder how they have implemented that function? If they rely on sp_depends (ie sysdepends table), then it doesn't work
/Kenneth
-
This code also works... Like the code that Kenneth posted, you should be made aware that if the, say, stored procedure is longer the 8k bytes and the table name you are looking is split at the 8k boundary, then neither routine will find it.
--Find name of proc, view, function, etc containing seached for text in DDL SELECT DISTINCT so.Name, so.Xtype FROM SYSOBJECTS so, SYSCOMMENTS sc WHERE so.ID = sc.ID AND sc.Text LIKE '%putwhatyouwanttofindhere%' ORDER BY so.xtype,so.Name
--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) -
Kenneth is absolutely correct. The system tables are updated for any given object ONLY when that object is installed/re-installed. I found out that one of our System Analysts has been using the dependencies in QA for determining what is being referenced and proved it wrong.
The way I find what is calling a table is the tried/true/blockhead way. Dump the scripts to text files (one per object) and then use windows search for the text you are interested in.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I agree that dependencies related tables are (more or less) unreliable but the SYSOBJECTS and SYSCOMMENTS tables are always up to date with the latest code for any given object. The two scripts posted above will do what you want...
--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) -
Try This
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempDBImpact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TempDBImpact]
GO
CREATE procedure dbo.TempDBImpact
@tab2bs varchar(100) = null -- table to be searched
AS
Set NOCOUNT On
Declare @procname varchar(100)
create table #Procfound (affected_procedures varchar(100))
-- Finding all the TNT Stored Procedures
DECLARE ProcNameCur CURSOR LOCAL
FOR
select name from sysobjects where xtype = 'P' or xtype = 'V' or xtype = 'FN'
FOR READ ONLY
OPEN ProcNameCur
FETCH NEXT FROM ProcNameCur
INTO @procname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Procfound
select @procname from syscomments
where id = object_id (@procname)
and
( charindex(upper(' '+@tab2bs+'.'),upper(text))!=0
OR charindex(upper(','+@tab2bs+'.'),upper(text))!=0
OR charindex(upper(@tab2bs),upper(text))!=0
OR charindex(upper(' '+@tab2bs+' '),upper(text))!=0
OR charindex(upper(' '+@tab2bs+','),upper(text))!=0
OR charindex(upper(','+@tab2bs+','),upper(text))!=0
OR charindex(upper(','+@tab2bs+' '),upper(text))!=0
OR charindex(upper(' '+@tab2bs+char(13)),upper(text))!=0
OR charindex(upper(char(9)+@tab2bs+char(13)),upper(text))!=0
OR charindex(upper(char(9)+@tab2bs+char(9)),upper(text))!=0
OR charindex(upper(char(9)+@tab2bs+' '),upper(text))!=0
OR charindex(upper(char(9)+@tab2bs+','),upper(text))!=0
OR charindex(upper(char(9)+@tab2bs+'.'),upper(text))!=0
 
FETCH NEXT FROM ProcNameCur
INTO @procname
END
CLOSE ProcNameCur
DEALLOCATE ProcNameCur
select distinct substring(affected_procedures,1,100) AS 'PROCEDURES-TO-CHANGE'
from #Procfound
Drop table #Procfound
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply