61 lines
1.4 KiB
Transact-SQL
61 lines
1.4 KiB
Transact-SQL
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 |