检索数据库中无效的视图

--检索数据库中无效的视图

IF OBJECT_ID('PROC_Validate_VIEW') IS NOT NULL

    DROP PROC PROC_Validate_VIEW

GO

CREATE PROC PROC_Validate_VIEW

AS

SET NOCOUNT ON

CREATE TABLE #TB

(

    Viewname NVARCHAR(100)

)

DECLARE @Tablename NVARCHAR(100)

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

       EXEC('SELECT TOP 0 * INTO # FROM ['+@Tablename+']')

    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

 

/*

Viewname

----------------------------

v_b

v_c

v_d

*/