--检索数据库中无效的视图
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
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/05/27/5629087.aspx