Here's what I found: The Nir method is best since it finds real dependencies (not from the text of the stored procedure), although it will not work properly unless you update the sql module. nip and Philip solutions are the same - find the line in the code of the stored procedure, it will not work properly if you have the same column name in several tables.
So, I decided to use the Nir solution and add a script inside usp_FindReferences to update the sql modules. Here is my last script:
USE [Cetgroups3] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[usp_depends2] --- 1996/08/09 16:51 @objname nvarchar(776) as declare @objid int declare @found_some bit declare @dbname sysname DECLARE @sp_depends_xref table ( reftype char(2), dep_name nvarchar(256), type char(16), updated char(7), selected char(8), [column] nvarchar(128)) select @dbname = parsename(@objname,3) if @dbname is not null and @dbname <> db_name() begin raiserror(15250,-1,-1) return (1) end select @objid = object_id(@objname) if @objid is null begin select @dbname = db_name() raiserror(15009,-1,-1,@objname,@dbname) return (1) end select @found_some = 0 set nocount on if exists (select * from sysdepends where id = @objid) begin raiserror(15459,-1,-1) INSERT INTO @sp_depends_xref (refType, dep_name , type, updated, selected, [column]) select 'TO', 'name' = (s6.name+ '.' + o1.name), type = substring(v2.name, 5, 16), updated = substring(u4.name, 1, 7), selected = substring(w5.name, 1,8), 'column' = col_name(d3.depid, d3.depnumber) from sysobjects o1, master.dbo.spt_values v2, sysdepends d3, master.dbo.spt_values u4, master.dbo.spt_values w5, --11667 sysusers s6 where o1.id = d3.depid and o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = 'O9T' and u4.type = 'B' and u4.number = d3.resultobj and w5.type = 'B' and w5.number = d3.readobj|d3.selall and d3.id = @objid and o1.uid = s6.uid and deptype < 2 select @found_some = 1
end
/ * ** Now check things depending on the object. * / if exists (select * from sysdepends, where depid = @objid) start
RAISERROR (15460, -1, -1)
INSERT INTO @sp_depends_xref (RefType, dep_name, type)
select "BY", "name" = (s.name + '.' + o.name), type = substring (v.name, 5, 16)
from sysobjects o, master.dbo.spt_values v, sysdepends d,
sysusers s
where o.id = d.id
and o.xtype = substring (v.name, 1,2) collate database_default and v.type = 'O9T'
and d.depid = @objid
and o.uid = s.uid
and deptim <2
select @found_some = 1 end
/ * ** Did we find anything in sysdepends? * / if @found_some = 0
raiserror (15461, -1, -1)
SELECT reftype, dep_name, type, updated, selected, [column]
FROM @sp_depends_xref
set nocount off
return (0) - sp_depends
GO
/ ** Object: StoredProcedure [dbo]. [usp_FindReferences] script Date: 11/18/2009 11:55:05 ** / SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATION PROCEDURE [dbo]. [Usp_FindReferences]
- Add stored procedure parameters here
@tablename nvarchar (500) = 0,
@colname nvarchar (500) = 0 LIKE TO START
- SET NOCOUNT ON is added to prevent the appearance of additional result sets - interference with SELECT statements.
SET NOCOUNT ON,
- Before starting - update the sql module to declare @sql as nvarchar (max); set @sql = ''; select @sql = @sql + N'begin try exec sp_refreshsqlmodule @name = '' '+ CAST (name like nvarchar (4000)) + N' ''; try to start catching print '' Failed to update '+ CAST (name like nvarchar (4000)) + N': '' + ERROR_MESSAGE (); IF XACT_STATE () = -1 ROLLBACK; final catch; 'from sys.sysobjects, where type in (' P ',' V ',' TF ',' FN '); - order by name; exec sp_executesql @sql; - Now we can go to the new data to create the table #tempTableDependencies (
reftype nvarchar (20),
dep_name nvarchar (500),
type nvarchar (500),
updated nvarchar (500),
selected nvarchar (500),
col nvarchar (500))
Paste in #tempTableDependencies do usp_depends2 @tablename
create table #tempDependencies (
reftype nvarchar (20),
dep_name nvarchar (500),
type nvarchar (500),
updated nvarchar (500),
selected nvarchar (500),
col nvarchar (500))
declare table @tempFilteredDependencies (
objectname nvarchar (500),
reftype nvarchar (20),
dep_name nvarchar (500),
type nvarchar (500),
updated nvarchar (500),
selected nvarchar (500),
col nvarchar (500))
DECLARE @loopcounter INT
select @loopcounter = COUNT (*) FROM #tempTableDependencies
DECLARE @dependencyname nvarchar (500)
WHILE @loopcounter> 0
TO BEGIN
SELECT TOP 1 @dependencyname = dep_name FROM #tempTableDependencies
print 'loop_counter =' + CAST (@loopcounter as nvarchar (20))
print 'dependency =' + @dependencyname
Paste in #tempDependencies do usp_depends2 @dependencyname
insert into @tempFilteredDependencies select @dependencyname as objectname, * from
End
select * from @tempFilteredDependencies order by objectname drop table #tempDependencies
drop table #tempTableDependencies
End
GO