怎样判断视图是否有效

[telankes2000]

数据库里有很多以前写的视图，
当初那些视图所用到的表的结构有的已经改变了，
有的字段已经删除了， 现在再查询视图的时候就会报错
但又不知道有多少这样的视图
我写了个存储过程来检查有多少个有错误的视图
但是执行有错误

create PROC  PROC_Validate_VIEW
AS
CREATE TABLE #TB(Tablename NVARCHAR(100))

DECLARE @Tablename NVARCHAR(100),@SQL NVARCHAR(200)

DECLARE  CUR  CURSOR FOR
SELECT        NAME FROM SYSOBJECTS WHERE    (XTYPE = 'V') ORDER BY  NAME
OPEN CUR
FETCH NEXT FROM CUR INTO @Tablename
WHILE 1=1
	BEGIN
		IF(@@FETCH_STATUS<>0)BREAK;
		SET @SQL = 'SELECT TOP 1 * into #temp FROM [' + @Tablename +']'
		EXEC(@SQL)
		IF(@@ERROR<>0)
		BEGIN
			INSERT INTO #TB
			SELECT @Tablename--记录有错误的视图
		END
		ELSE
		BEGIN--没有错的就删除临时表
			DELETE FROM #temp--这里报错 临时表不存在 应该怎么写呢
			
		END
		FETCH NEXT FROM CUR INTO @Tablename
	END
CLOSE CUR
DEALLOCATE CUR
SELECT * FROM #TB
DROP TABLE #TB

[pt1314917]

create PROC  PROC_Validate_VIEW
AS
	CREATE TABLE #TB(Tablename NVARCHAR(100))
	DECLARE @Tablename NVARCHAR(100),@SQL NVARCHAR(200)
	DECLARE  CUR  CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE (XTYPE = 'V') ORDER BY  NAME
	OPEN CUR
	FETCH NEXT FROM CUR INTO @Tablename
	WHILE(@@FETCH_STATUS=0)
	BEGIN
		SET @SQL = 'SELECT TOP 1 * into ##temp FROM [' + @Tablename +']'
		EXEC(@SQL)
		IF(@@ERROR<>0)
		BEGIN
			INSERT INTO #TB SELECT @Tablename
		END
		ELSE
		BEGIN
			DELETE FROM ##temp--可以换成全局临时表
		END
		FETCH NEXT FROM CUR INTO @Tablename
	END
	CLOSE CUR
	DEALLOCATE CUR
	SELECT * FROM #TB
go

[htl258_Tony]

CREATE PROC  PROC_Validate_VIEW
AS
CREATE TABLE #TB
(
	Tablename NVARCHAR(100)
)

DECLARE @Tablename NVARCHAR(100), @SQL  NVARCHAR(200)

DECLARE CUR CURSOR FOR
    SELECT NAME 
    FROM SYSOBJECTS
    WHERE (XTYPE='V')
    ORDER BY NAME
OPEN CUR
FETCH NEXT FROM CUR INTO @Tablename
WHILE @@FETCH_STATUS=0
BEGIN
    SET @SQL = 'SELECT TOP 1 * into #temp FROM ['+@Tablename+']'
    EXEC (@SQL)
    IF (@@ERROR<>0)
    BEGIN
        INSERT INTO #TB
        SELECT @Tablename--记录有错误的视图
    END
    FETCH NEXT FROM CUR INTO @Tablename
END
CLOSE CUR
DEALLOCATE CUR
SELECT * 
FROM #TB
DROP TABLE #TB
GO

不需要再删除#temp，局部临时表在EXEC后会自动消失的。

[htl258_Tony]

--如果系统是SQL2005以上的，建议用这个过程：

ALTER PROC  PROC_Validate_VIEW
AS
SET NOCOUNT ON 
CREATE TABLE #TB
(
    Tablename NVARCHAR(100)
)
DECLARE @Tablename NVARCHAR(100), @SQL  NVARCHAR(200)
DECLARE CUR CURSOR FOR
    SELECT NAME FROM SYSOBJECTS
    WHERE (XTYPE='V')
    ORDER BY NAME
OPEN CUR
FETCH NEXT FROM CUR INTO @Tablename
WHILE @@FETCH_STATUS=0
BEGIN
	BEGIN TRY
		SET @SQL = 'SELECT TOP 0 * into #t FROM ['+@Tablename+']'
		EXEC (@SQL)		
	END TRY
	BEGIN CATCH
		INSERT INTO #TB SELECT @Tablename--记录有错误的视图
	END CATCH
    FETCH NEXT FROM CUR INTO @Tablename
END
CLOSE CUR
DEALLOCATE CUR
SELECT * FROM #TB
SET NOCOUNT OFF
GO

EXEC PROC_Validate_VIEW