Find Dependent Object In SQL Server

CREATE PROCEDURE sam_proc_FindTableProcedure
@objname VARCHAR(1000)  
AS  
DECLARE @objid INT
DECLARE @found_some bit   
DECLARE @dbname sysname  

SELECT @dbname = parsename(@objname,3)  
  
IF @dbname IS NOT NULL AND @dbname <> db_name()  
 BEGIN  
  raiserror(15250,-1,-1)  
  RETURN (1)  
 end  

SELECT @objid = object_id(@objname)  
IF @objid IS NULL  
 BEGIN  
  SELECT @dbname = db_name()  
  raiserror(15009,-1,-1,@objname,@dbname)  
  RETURN (1)  
 end  
  
SELECT @found_some = 0  
  
SET NOCOUNT ON  

IF exists (SELECT *  
  FROM sysdepends  
   WHERE id = @objid)  
BEGIN  
 RAISERROR(15459,-1,-1)  
 SELECT   'name' = (s6.name+ '.' + o1.name),  
    type = substring(v2.name, 5, 66), 
    updated = substring(u4.name, 1, 7),  
    selected = substring(w5.name, 1, 8),  
             'column' = col_name(d3.depid, d3.depnumber)  
  FROM  sys.objects  o1  
   ,master.dbo.spt_values v2  
   ,sysdepends  d3  
   ,master.dbo.spt_values u4  
   ,master.dbo.spt_values w5 
   ,sys.schemas  s6  
  WHERE  o1.object_id = d3.depid  
  AND  o1.type = substring(v2.name,1,2) collate database_default AND v2.type = 'O9T'  
  AND  u4.type = 'B' AND u4.number = d3.resultobj  
  AND  w5.type = 'B' AND w5.number = d3.readobj|d3.selall  
  AND  d3.id = @objid  
  AND  o1.schema_id = s6.schema_id  
  AND deptype < 2  
  
 SELECT @found_some = 1  
END  
IF exists (SELECT *  
  FROM sysdepends  
   WHERE depid = @objid)  
BEGIN  
  raiserror(15460,-1,-1)  
 SELECT distinct 'name' = (s.name + '.' + o.name),  
  type = substring(v.name, 5, 66)   
   FROM sys.objects o, master.dbo.spt_values v, sysdepends d,  
    sys.schemas s  
   WHERE o.object_id = d.id  
    AND o.type = substring(v.name,1,2) collate database_default AND v.type = 'O9T'  
    AND d.depid = @objid  
    AND o.schema_id = s.schema_id  
    AND deptype < 2  
  
 SELECT @found_some = 1  
END     
IF @found_some = 0  
 RAISERROR(15461,-1,-1)  
 SET NOCOUNT OFF
  
RETURN (0)  
Ebook Download
View all
Learn
View all