64 lines
1.6 KiB
Transact-SQL
64 lines
1.6 KiB
Transact-SQL
--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
|
|
|