查看与某一个表相关的视图、存储过程、函数
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;