Stamey (7/20/2011)
I already have code that searches for the column in procs, indexes, and UDFs. My concern is for other objects, such as constraints and primary keys, or rather any other type of object that will cause the field expansion to fail. This is how I came up with the index code. I did not think these fields were indexed in the various DBs so I generated change code for the expansion and when deployment ran it they received a failure because one of the fields participated in an index, and SQL Server would not expand it.Once I had created the index code I thought about the fact that there could be other types of objects having dependencies on the column so I began researching how to find out what they are. Looks at scripts and articles from here, sys.sql_dependencies, sys.dm_sql_referencing_entities, sys.dm_sql_referenced_entities and the actual code of sp_depends has not given me what I believe I need to get a complete picture of dependencies on each column.
That was the point where I decided to post the question here.
Thanks,
Chris
I have this snippet for checking columns based on references in sysdepends;
i haven't bothere dto update it to use the newer sys.sql_expression_dependencies view for 2005+
this was checking a specific column, and not all columns, but i think this will help:
declare @tbl_nme as varchar(50)
declare @col_nme as varchar(50)
declare @level int
set @level = 1
set @tbl_nme='YOURTABLENAME'
set @col_nme= 'YOURCOLUMNNAME'
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
, @level as level
into #temp
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where obj.name = @tbl_nme
and col.name = @col_nme
while (@@rowcount > 0)
begin
set @level = @level + 1
insert into #temp
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
, @level as level
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where exists(select 1 from #temp a where obj.name = a.dep_obj_nm and
col.name = a.dep_col_nm and level = @level - 1 and dep_col_nm is not null)
end
select * from #temp
drop table #temp
Lowell