44 lines
1.1 KiB
Transact-SQL
44 lines
1.1 KiB
Transact-SQL
DECLARE SchemaCursor CURSOR FOR
|
|
SELECT DISTINCT
|
|
i.C_AZONOSITO AS IntezmenyAzonosito
|
|
,i.ID AS IntezmenyId
|
|
,t2.ID AS TanevId
|
|
FROM T_TANEV t
|
|
INNER JOIN T_INTEZMENY i ON i.ID = t.C_INTEZMENYID
|
|
INNER JOIN T_TANEV t2 ON t2.C_INTEZMENYID = t.C_INTEZMENYID AND t2.TOROLT = 'F' AND t2.C_AKTIV = 'T'
|
|
WHERE t.TOROLT = 'T'
|
|
AND i.TOROLT = 'F'
|
|
|
|
|
|
DECLARE
|
|
@schemaName nvarchar(100)
|
|
,@intezmenyAzonosito nvarchar(100)
|
|
,@intezmenyId int
|
|
,@tanevID int
|
|
,@sql nvarchar(max)
|
|
|
|
OPEN SchemaCursor
|
|
FETCH NEXT FROM SchemaCursor INTO @intezmenyAzonosito, @intezmenyId, @tanevID
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
SET @sql = ''
|
|
SET @schemaName = 'KR_' + @intezmenyAzonosito + '_Schema'
|
|
|
|
SELECT @sql += 'DROP VIEW [' + @schemaName + '].' + name + char(13) + char(10) + ';'
|
|
FROM sys.views
|
|
WHERE schema_id = SCHEMA_ID(@schemaName)
|
|
|
|
EXEC sp_executesql @sql
|
|
|
|
EXEC [dbo].[sp_Global_CreateSchemaViews]
|
|
@IntezmenyId = @intezmenyId
|
|
,@IntezmenyAzonosito = @intezmenyAzonosito
|
|
,@TanevId = @tanevID
|
|
|
|
FETCH NEXT FROM SchemaCursor INTO @intezmenyAzonosito, @intezmenyId, @tanevID
|
|
END
|
|
|
|
CLOSE SchemaCursor
|
|
DEALLOCATE SchemaCursor
|
|
GO
|