Here is something that might help you. I have created two user stored procs that do something similar to what you are asking.
usp_depends2 - an extended version of sp_depends
usp_FindReferences - this one uses usp_depends2 to find all references for a column in a table (I think this is what you need)
/****** Object: StoredProcedure [dbo].[usp_depends2] Script Date: 11/18/2009 11:55:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[usp_depends2] --- 1996/08/09 16:51
@objname nvarchar(776) /* the object we want to check */
as
declare @objid int /* the id of the object we want */
declare @found_some bit /* flag for dependencies found */
declare @dbname sysname
/*
** Make sure the @objname is local to the current database.
*/
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
/*
** See if @objname exists.
*/
select @objid = object_id(@objname)
if @objid is null
begin
select @dbname = db_name()
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
/*
** Initialize @found_some to indicate that we haven't seen any dependencies.
*/
select @found_some = 0
set nocount on
/*
** Print out the particulars about the local dependencies.
*/
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 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;
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 #tempDependencies where col = @colname and dep_name like '%' + @tablename
delete from #tempDependencies
delete from #tempTableDependencies where dep_name = @dependencyname
SET @loopcounter = @loopcounter - 1
END
select * from @tempFilteredDependencies
drop table #tempDependencies
drop table #tempTableDependencies
END
GO