Dependency های یک Table.

[Elham_gh]

یک StoreProcedure برای تشخیص Dependency های یک Table. امیدوارم بدردتون بخوره.




create procedure sp_tableDependencies(
@tableName sysname
)
as
begin
declare @rowsProcessed int

create table #tables (
processed int,
tableLevel int,
childTable sysname,
parentTable sysname,
)

insert into #tables
select 0, 1, childs.name childTable, parents.name parentTable
from sysobjects childs inner join sysforeignkeys fkeys
on childs.id = fkeys.fkeyid inner join sysobjects parents
on fkeys.rkeyid = parents.id
where (childs.name = @tableName)
set @rowsProcessed = @@rowcount

while (@rowsProcessed > 0)
begin
update #tables
set processed = 1
where processed = 0

insert into #tables
select distinct 0, 1, childs.name childTable, parents.name parentTable
from sysobjects childs inner join sysforeignkeys fkeys
on childs.id = fkeys.fkeyid inner join sysobjects parents
on fkeys.rkeyid = parents.id inner join #tables
on childs.name = #tables.parentTable
where (#tables.processed = 1)
and (childs.name <> parents.name)
order by childs.name
set @rowsProcessed = @@rowcount

update #tables
set processed = 2
where processed = 1

end

select childTable, parentTable from #tables
drop table #tables
end

[AminSobati]

و برای تکمیل مورد ذکر شده، اگر وابستگی به سایر Objectها هم مد نظر باشه:


EXEC SP_DEPENDS 'MyTable'

EXEC SP_DEPENDS 'MyProc'