Here is what I found:
Nir method is best as it finds the real dependencies (not by the text of the stored procedure), though it will not work properly if you dont refresh sql module.
nip and Philip solutions are the same - find a string in the stored procedure code, it will not work properly if you have the same column name in several tables.
So I decided to use Nir's solution and add my script inside usp_FindReferences to refresh sql modules.
Here is my final script:
USE [Cetgroups3]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_depends2]
@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,
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 for things that depend on the object. */
if exists (select * from sysdepends where depid = @objid)
begin
raiserror(15460,-1,-1)
INSERT INTO @sp_depends_xref (RefType, dep_name, type)
select distinct '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 deptype < 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
CREATE PROCEDURE [dbo].[usp_FindReferences]
-- Add the parameters for the stored procedure here
@tablename nvarchar(500) = 0,
@colname nvarchar(500) = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
SET NOCOUNT ON;
-- Before starting - refresh sql module
declare @sql as nvarchar(max);
set @sql = '';
select @sql = @sql + N'begin try
exec sp_refreshsqlmodule @name = ''' + CAST(name as nvarchar(4000)) + N''';
end try
begin catch
print ''Failed to refresh ' + CAST(name as nvarchar(4000)) + N': '' + ERROR_MESSAGE();
IF XACT_STATE() = -1 ROLLBACK;
end catch;
'
from sys.sysobjects where type in ('P', 'V', 'TF', 'FN');-- order by name;
exec sp_executesql @sql;
-- Now we can proceed with fresh data
create table #tempTableDependencies (
reftype nvarchar(20),
dep_name nvarchar(500),
type nvarchar(500),
updated nvarchar(500),
selected nvarchar(500),
col nvarchar(500))
insert into #tempTableDependencies execute 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 @tempFilteredDependencies table (
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
BEGIN
SELECT TOP 1 @dependencyname = dep_name
FROM #tempTableDependencies
print 'loop_counter = ' + CAST(@loopcounter as nvarchar(20))
print 'dependency = ' + @dependencyname
insert into #tempDependencies execute usp_depends2 @dependencyname
insert into @tempFilteredDependencies
select @dependencyname as objectname, *
from
where col = @colname
and dep_name like '%' + @tablename
delete from
delete from
where dep_name = @dependencyname
SET @loopcounter = @loopcounter - 1
END
select * from @tempFilteredDependencies order by objectname
drop table #tempDependencies
drop table #tempTableDependencies
END
GO
