SQLSERVER TEST ALL VIEWS

علی ذوالفقار
1402/04/28 13:10:47 (126)
-- TEST ALL VIEWS 
-- SELECT * FROM INFORMATION_SCHEMA.TABLES 

DECLARE @TABLE_NAME NVARCHAR(255) 
DECLARE @SQLSTR NVARCHAR(4000)
DECLARE @ID_COL NVARCHAR(255)

DECLARE CUR CURSOR FOR 
	SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' ORDER BY TABLE_NAME 	
	
OPEN CUR 
FETCH NEXT FROM CUR INTO @TABLE_NAME 

WHILE @@FETCH_STATUS = 0 
	BEGIN 
		PRINT('TEST VIEW : ' + @TABLE_NAME)
		SET @SQLSTR = 'SELECT TOP 1 * FROM ' + @TABLE_NAME
		BEGIN TRY			
			-- SET FMTONLY ON -- DO NOT DISPLAY OUTPUTS 
			EXEC(@SQLSTR)			
			-- SET FMTONLY OFF 
		END TRY 
		BEGIN CATCH
			PRINT('ERROR IN VIEW ' + @TABLE_NAME)
			PRINT(ERROR_MESSAGE())
			PRINT(ERROR_NUMBER())
		END CATCH 
		PRINT('----------------------------------------')
		-- FETCH NEXT ITEM IN CURSOR 
		FETCH NEXT FROM CUR INTO @TABLE_NAME 
	END 	
CLOSE CUR 
DEALLOCATE CUR         
Back