查看与某一个表相关的视图、存储过程、函数

查看与某一个表相关的视图、存储过程、函数

SQL:

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

重新编译相关视图：

Create PROCEDURE RefreshAllView
@TableName nvarchar(50) 
AS
BEGIN
 DECLARE MyCursor CURSOR 
  FOR 
--整个库所有视图存储过程。
--select Name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsView') = 1 and (not name in ('sysconstraints','syssegments')) order by name
select a.name from sysobjects a, syscomments b where a.id = b.id and b.text like '%'+@TableName+'%'
 DECLARE @name varchar(40) 
 OPEN MyCursor  
 FETCH NEXT FROM MyCursor INTO @name 
 WHILE (@@fetch_status <> -1) 
 BEGIN 
  IF (@@fetch_status <> -2) 
  begin 
   exec sp_refreshview @name 
  end  
  FETCH NEXT FROM MyCursor INTO @name 
 END 
 CLOSE MyCursor 
 DEALLOCATE MyCursor 
END

Oracle:

SET SERVEROUT ON  
BEGIN  
 FOR I IN (select * from user_views) LOOP  
 IF INSTR(I.text, '&tab_name') > 0 THEN  
  DBMS_OUTPUT.PUT_LINE(I.view_name);  
END IF;  
END LOOP;  
END;