﻿IF OBJECT_ID('T_ENTITYATTRIBUTEHISTORY_OSSZES') IS NOT NULL
BEGIN
	DROP VIEW T_ENTITYATTRIBUTEHISTORY_OSSZES
END

IF OBJECT_ID('T_LOG_OSSZES') IS NOT NULL
BEGIN
	DROP VIEW T_LOG_OSSZES
END

IF OBJECT_ID('T_ENTITYHISTORY_OSSZES') IS NOT NULL
BEGIN
	DROP VIEW T_ENTITYHISTORY_OSSZES
END

DECLARE @tableName NVARCHAR(200)
DECLARE @viewName NVARCHAR(200)
DECLARE @sql NVARCHAR(max)

DECLARE tableCursor CURSOR LOCAL FOR 
	SELECT t.name 
	FROM sys.tables t
	INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
	WHERE s.name ='dbo'
		AND t.name NOT IN ('T_ENTITYATTRIBUTEHISTORY', 'T_ENTITYHISTORY', 'T_LOG')
	ORDER BY s.name

OPEN tableCursor

FETCH NEXT FROM tableCursor INTO @tableName
WHILE @@FETCH_STATUS = 0 
BEGIN
	PRINT @tableName

	SET @viewName = @tableName + N'_OSSZES'

	SET @sql = N'IF OBJECT_ID(''' + @viewName + ''') IS NOT NULL
	BEGIN
		DROP VIEW ' + @viewName + '
	END'
    
	--PRINT @sql
    EXECUTE sp_executesql @sql

	IF EXISTS (SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @tableName AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME IN ( 'C_INTEZMENYID', 'C_TANEVID', 'C_ALINTEZMENYID', 'C_ALTANEVID' ))
    BEGIN
        SET @Sql = N'CREATE VIEW [dbo].' + @viewName + '
        AS
        SELECT *
        FROM dbo.' + @tableName
        EXECUTE sp_executesql @Sql
    END

	FETCH NEXT FROM tableCursor INTO @tableName
END

CLOSE tableCursor
DEALLOCATE tableCursor
GO