--Migration
-- =========================================
-- Oszlopok törlése
-- =========================================
DECLARE @sql nvarchar(max) = ''

SELECT @sql += 'ALTER TABLE ' + t.name + ' DROP COLUMN IF EXISTS NNID;' + CHAR(13) + CHAR(10) 
FROM sys.tables t
  INNER JOIN sys.columns c On t.object_id = c.object_id 
WHERE c.name = 'NNID'

EXEC sp_executesql @sql
GO

ALTER TABLE T_FELHASZNALO DROP COLUMN IF EXISTS FTIP;
GO

ALTER TABLE T_ALKALMAZOTT DROP COLUMN IF EXISTS NNIDA
GO

ALTER TABLE T_CIM DROP COLUMN IF EXISTS GID
GO

ALTER TABLE T_TANEVRENDJE DROP COLUMN IF EXISTS CSOP
GO

-- =========================================
-- mig schema törlése
-- =========================================
DECLARE @schema nvarchar(100) = 'mig'

/* SP és FN törlések  */
DECLARE @sql nvarchar(max) = '';

SELECT @sql += 'DROP ' + ROUTINE_TYPE + ' [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']' + char(13) + char(10) 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = @schema

EXEC sp_executesql @sql

/* VIEW törlések */
SET @sql = '';

SELECT @sql += 'DROP ' + CASE TABLE_TYPE WHEN 'BASE TABLE' THEN 'TABLE [' WHEN 'VIEW' THEN 'VIEW [' ELSE NULL END + TABLE_SCHEMA + '].[' + TABLE_NAME +']'  + char(13) + char(10) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = @schema

EXEC sp_executesql @sql

/*SCHEMA törlése*/
SET @sql = '';
SELECT @sql += 'DROP SCHEMA IF EXISTS [' + @schema + ']' + char(13) + char(10) 

EXEC sp_executesql @sql
GO


-- =========================================
-- View-k újragenerálása
-- =========================================
EXEC dev.uspCreateSchemaViews NULL, NULL, 0
GO