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