Wednesday, April 22, 2015

sql server script to find object dependencies


--use lab10
--select * from sys.objects where type  = 'p'
-- pr_obj_dependencies 'dbo.pp2'


--create proc pp2 as exec pp1
--create proc pp1
--as
--exec p1
--pr_obj_dependencies 
--select * from t1111






alter  procedure pr_obj_dependencies (@objname varchar(256) = null)
as  
set nocount on  
set transaction isolation level read uncommitted
-- 1  

----------------------------------------------------------------------------------------------------------------------------
--validate each object
--create table t1111  (objname varchar(500))
create table #t1(objname varchar(500))
declare @name varchar(256)
declare cur cursor for select isnull(schema_name(schema_id),'dbo')+'.'+name from sys.objects where type   IN ('U','P','FN')
open cur

fetch next from cur into @name 

while  @@FETCH_STATUS = 0 
begin
begin try

--insert into #t1   select @name 
if (select COUNT(*) from  sys.dm_sql_referenced_entities(@name,'OBJECT') b) > -1
begin
print  @name 
insert into #t1 select @name 
--select * from #t1
--select @name 
end 
end try

begin catch
select @name as err

end catch


fetch next from cur into @name 

continue
end


close cur

deallocate cur

----------------------------------------------------------------------------------------------------------------------------
SELECT ISNULL(b.referenced_schema_name,'DBO')AS FSC_NAME,
OBJECT_ID(ISNULL(b.referenced_schema_name,'DBO')+ '.'+b.referenced_entity_name  ) AS FK,
schema_name(a.schema_id) AS RSC_NAME,A.OBJECT_ID AS PK  
into #sysref 
from ( select * FROM SYS.OBJECTS where  schema_name(schema_id)+'.'+name in (select objname from #t1))A CROSS APPLY
sys.dm_sql_referenced_entities(schema_name(a.schema_id)+'.'+A.name,'OBJECT') b

where b.referenced_minor_name is null -- eliminate column names
AND A.name <> 'sp_upgraddiagrams'
--and schema_name(b.schema_id)+'.'+b.name = @objname 


--select *,OBJECT_NAME(FK),OBJECT_NAME(PK) from #sysref 
--SELECT * FROM #sysref
--SELECT OBJECT_NAME(FK),OBJECT_NAME(PK) FROM #sysref

--*******************************************************  
-- Get the Max No.Of Joins foreign keys in a Chain  
declare @N VARCHAR(9);  
WITH CTE(FKEYID,LEVEL)  
    AS 
     (SELECT OBJECT_ID,0 FROM sys.OBJECTS where type   IN ('U','P','FN')
    UNION ALL  
    select T1.PK ,LEVEL+1 FROM #sysref T1 JOIN CTE T2 ON T1.FK = T2.FKEYID ) 
 -- Statement that executes the CTE 

 SELECT @N = Max(level)  FROM CTE  
PRINT @N  
--*******************************************************  

Declare @V1 varchar(max)  
set @V1 = '' 
  
Declare @V2 varchar(max)  
set @V2 = '' 
 declare @select varchar(max)  
set @select = '' 
 Declare @OrderBy varchar(2)  
set  @OrderBy = @N+1 
 WHILE NOT @N = 0  
BEGIN 
             set @V1 =  @V1 + ' right outer join #sysref A' +cast(@N as varchar(4)) + ' on a'+ @V2+'.pk =A'+cast(@N as varchar(4))+'.fk' 
             set @select =  @select+    'a'+ @N+'.FSC_NAME+''.''+object_name('+'a'+ @N+'.fk),' 
 set @V2 = @N 
 SET @N = @N -1  
CONTINUE  
END  
SET @select=  @select +  'A1.RSC_NAME+''.''+object_name(A1.PK)'  



exec('SELECT ' + @select +'  from #sysref a ' +@V1 + ' order by '+ @OrderBy )  


print ('SELECT ' + @select +'  from #sysref a ' +@V1 + ' order by '+ @OrderBy )  

exec('SELECT ' + @select +'  from #sysref a ' +@V1 + ' where object_name(A1.PK)='''+@objname +''' order by '+ @OrderBy )  


print ('SELECT ' + @select +'  from #sysref a ' +@V1 + ' where object_name(A1.PK)='''+@objname +''' order by '+ @OrderBy )  


submitted to the Forums team, ask forsdf
Post a Comment

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...

Contributors