Como descobrir quais são as dependências de uma tabela

Coloca no seu banco e rode

Abraços

-------------------------------------------------



/*

-- drop proc up_relacionamentos


*/

create procedure up_relacionamentos_B
as
/*
Procedure destinada a criação de listagem das tabelas e seus relacionamentos.
São selecionadas apenas as tabelas que possuem ao menos um registro.
Ceilton Leal Marinho
22/04/2005

*/

begin

SET NOCOUNT on --r1.constid,
set fmtonly off
declare @nome varchar(30),@linhas integer,@idtabela integer, @cont integer, @texto varchar(100),
@id1 integer, @fk integer,@id2 integer, @co2 integer, @strnome varchar(250),
@namecol varchar(250), @totaltabelas smallint,
@nomeindice varchar(50),
@campo varchar(100),
@descchave varchar(100),
@linha varchar(100),
@nomecol char(20),
@tipocol char(12),
@tamcol char(10),
@nullcol char(10),
@indid integer,

@procedures varchar(100),
@nameproc varchar(30),
@idproc integer ,
@tipo varchar(2),
@colorder tinyint ,
@descricao varchar(100)

select @cont = 1


--Seleciona o nome das tabelas que possuem um registro ou mais -----------------
declare cursor01 cursor for
select t.name , i.rows, t.id
from sysobjects t left join sysindexes i on t.id = i.id
where type = ´U´ and indid <= 1
--and i.rows > 0
order by t.name

select @totaltabelas = count(*) from sysobjects t left join sysindexes i on t.id = i.id
where type = ´U´ and indid <= 1
--and i.rows > 0




open cursor01

fetch cursor01 into @nome,@linhas,@idtabela

create table #resultado ( seq smallint,linha varchar(250), chave varchar(250))

insert resultado (seq,linha,chave) values (@cont , ´´,´´ )
select @cont = @cont + 1
insert #resultado (seq,linha,chave) values (@cont , ´´,´´ )
insert resultado (seq,linha,chave) values (@cont , ´´,´´ )

while @@FETCH_STATUS = 0 begin
select @cont = @cont + 1
select @nomeindice = ´´
-- nome da chave primaria
select @nomeindice = name from sysindexes
where id = @idtabela and indid > 0 and indid < 255 and (status & 64)=0
and indexproperty(@idtabela,name,´IsUnique´) = 1

-- Campos da chave primaria
declare cursorcampos cursor for
select c.name from sysindexes i, sysindexkeys k, syscolumns c
where i.id = @idtabela and i.indid > 0 and i.indid < 255 and (i.status & 64)=0
and indexproperty(@idtabela , i.name,´IsUnique´) = 1
and i.id = k.id and i.indid = k.indid
and c.id = i.id and c.colid = k.colid
open cursorcampos
fetch cursorcampos into @campo
select @descchave = ´´
while @@FETCH_STATUS = 0 begin
if @descchave <> ´´ select @descchave = @descchave + ´, ´
select @descchave = @descchave + @campo --+ ´ ´
fetch cursorcampos into @campo
end

insert #resultado values (@cont, ´TABELA: ´ + @nome + ´ ´ + convert(varchar(10),@linhas) ,´´ )
insert resultado values (@cont, ´Chave primaria: ´ + @nomeindice + ´ chave: ´ + @descchave , ´´)


-- Listagem dos indices da tabela
declare cursorindices cursor for
select name, indid from sysindexes
where id = @idtabela and indid > 0 and indid < 255 and (status & 64)=0
and indexproperty(@idtabela,name,´IsUnique´) = 0

open cursorindices
fetch cursorindices into @nomeindice , @indid

while @@FETCH_STATUS = 0 begin -- Loop dos indices de uma tabela

declare cursorcamposindice cursor for
select c.name from sysindexes i, sysindexkeys k, syscolumns c
where i.id = @idtabela
and i.indid = @indid
and i.id = k.id and i.indid = k.indid
and c.id = i.id and c.colid = k.colid

open cursorcamposindice
fetch cursorcamposindice into @campo

select @descchave = ´´
while @@FETCH_STATUS = 0 begin
if len(@descchave) > 5 select @descchave = @descchave + ´, ´
select @descchave = @descchave + @campo
fetch cursorcamposindice into @campo
end

close cursorcamposindice
deallocate cursorcamposindice

insert #resultado values (@cont, ´Indice: ´ + @nomeindice + ´ Chave: ´ + @descchave , ´´)
select @cont = @cont + 1
fetch cursorindices into @nomeindice , @indid
end

close cursorindices
deallocate cursorindices



--===============================================================================================================
select @cont = @cont + 1
insert #resultado values (@cont,´´,´´)
insert resultado values (@cont,´-----Dependências --------------------------------´,´´)

-- Cursor das tabelas que referenciam a tabela em questão
declare cursor02 cursor for
select o1.id , o2.id, r1.constid -- o1 referencia o2
from sysobjects o1 inner join sysreferences r1
on r1.fkeyid = o1.id inner join sysobjects o2
on r1.rkeyid = o2.id
where r1.fkeyid = @idtabela

open cursor02

fetch cursor02 into @id1, @id2, @fk

while @@FETCH_STATUS = 0 begin -- Loop das constraints que a tabela referencia
select @cont = @cont + 1
insert resultado
select @cont, o1.name + ´ -- referencia --> ´ + o2.name,´´
from sysobjects o1 inner join sysreferences r1
on r1.fkeyid = o1.id inner join sysobjects o2
on r1.rkeyid = o2.id
where r1.fkeyid = @idtabela and r1.constid = @fk

declare cursor03 cursor for-- pega as colunas
select fk.fkeyid, fk.fkey, co.name from sysforeignkeys fk, syscolumns co
where fk.constid = @fk and fk.fkeyid = co.id and co.colid = fk.fkey
open cursor03

fetch cursor03 into @id1, @co2, @namecol
select @strnome = ´Chave - ´

while @@FETCH_STATUS = 0 begin -- Loop das colunas da constraint
if len(@strnome)>9 select @strnome = @strnome + ´,´
select @strnome = @strnome + @namecol
fetch cursor03 into @id1, @co2, @namecol
end

close cursor03
DEALLOCATE cursor03

--update #resultado set chave = @strnome where seq = @cont
update resultado set linha = linha + ´ ´ + @strnome where seq = @cont

--select @cont = @cont + 1
fetch cursor02 into @id1, @id2, @fk
end
close cursor02
DEALLOCATE cursor02
close cursorcampos
DEALLOCATE cursorcampos
select @cont = @cont + 1
insert #resultado (seq,linha,chave) values (@cont , ´´,´´ )
--===========================================================================================

-- Cursor das tabelas que a tabela em questão referencia
declare cursor02 cursor for
select o2.id , o1.id, r1.constid -- o1 referencia o2
from sysobjects o1 inner join sysreferences r1
on r1.fkeyid = o1.id inner join sysobjects o2
on r1.rkeyid = o2.id
where r1.rkeyid = @idtabela

-- select * from sysreferences

open cursor02
fetch cursor02 into @id1, @id2, @fk

while @@FETCH_STATUS = 0 begin -- Loop das constraints que a tabela referencia
select @cont = @cont + 1

select @fk = constid from sysreferences where rkeyid = @id1 and fkeyid = @id2
and constid not in (select constid from resultado )

insert #resultado
select @cont, o2.name + ´ -- é referenciada por --> ´ + o1.name,´´
from sysobjects o1 inner join sysreferences r1
on r1.fkeyid = o1.id inner join sysobjects o2
on r1.rkeyid = o2.id
where r1.rkeyid = @idtabela and r1.constid = @fk

declare cursor03 cursor for-- pega as colunas
select fk.fkeyid, fk.fkey, co.name from sysforeignkeys fk, syscolumns co
where fk.constid = @fk and fk.fkeyid = co.id and co.colid = fk.fkey
open cursor03

fetch cursor03 into @id1, @co2, @namecol
select @strnome = ´Chave - ´

while @@FETCH_STATUS = 0 begin -- Loop das colunas da constraint
if len(@strnome) > 9 select @strnome = @strnome + ´,´
select @strnome = @strnome + @namecol
fetch cursor03 into @id1, @co2, @namecol
end

close cursor03
DEALLOCATE cursor03

--update #resultado set chave = @strnome where seq = @cont
update resultado set linha = linha + ´ ´ + @strnome where seq = @cont

--select @cont = @cont + 1
fetch cursor02 into @id1, @id2, @fk
end
close cursor02
DEALLOCATE cursor02


-- Procedures que referenciam a tabela ------------------------------------------------------

insert resultado (seq,linha,chave) values (@cont , ´´,´´ )

declare cursor06 cursor for
select distinct o.name, o.id, o.xtype
from sysobjects o , sysdepends d, sysobjects ot
where o.type = ´P´ and o.id = d.id and d.depid = @idtabela
and ot.id = d.depid

open cursor06
fetch cursor06 into @nameproc, @idproc, @tipo

if @@FETCH_STATUS = 0
insert resultado (seq,linha,chave) values (@cont , ´-- Procedures que referenciam a tabela -------------------´ ,´´ )

select @procedures = ´´
while @@FETCH_STATUS = 0 begin -- Loop das procedures que referenciam a tabela
if len(@procedures) > 5 select @procedures = @procedures + ´, ´
select @procedures = @procedures + @nameproc
select @cont = @cont + 1

if len(@procedures) > 90 begin
insert #resultado (seq,linha,chave) values (@cont , @procedures ,´´ )
select @procedures = ´´
end

fetch cursor06 into @nameproc, @idproc, @tipo
end

insert resultado (seq,linha,chave) values (@cont , @procedures ,´´ )

close cursor06
deallocate cursor06

--==============================================================================================================


select @cont = @cont + 1
insert resultado values (@cont,´´,´´)
insert resultado values (@cont,´´,´´)
insert resultado values (@cont, ´==================================================================´,´´ )

fetch cursor01 into @nome,@linhas,@idtabela



end
update #resultado set linha = ´Tabelas nesta relação: ´ + convert(varchar(30),@totaltabelas) where seq = 1
select linha from resultado order by seq --, chave

close cursor01
DEALLOCATE cursor01
-- drop table resultado
end




GO

Achu mais facil vc abrir a tabela e clicar em dependencia...

:)