220 lines
8.5 KiB
Transact-SQL
220 lines
8.5 KiB
Transact-SQL
-- Intézmény adatokat eltávolító szkript, ha csak pár intézmény kell az adatbázisba
|
|
declare @INTEZMENYEK varchar(max) = 'klik034885002'; -- <----- Megmaradó intézmény azonosítók
|
|
declare @SQL nvarchar(max);
|
|
|
|
|
|
|
|
-- EZUTÁN KÖVETKEZŐ SOROKHOZ NE NYÚLJ! -----------------------------------------------------------------------
|
|
-- ellenőrzés
|
|
select @SQL = 'Érvénytelen intézmény azonosító: ' + a.value
|
|
from string_split(@INTEZMENYEK, ',') a
|
|
where not exists(select * from T_INTEZMENY where C_AZONOSITO = a.value);
|
|
|
|
if @SQL > '' throw 50001, @SQL, 1;
|
|
|
|
set @INTEZMENYEK = stuff((
|
|
select concat(',', i.ID)
|
|
from string_split(@INTEZMENYEK, ',') a
|
|
inner join T_INTEZMENY i on i.C_AZONOSITO = a.value
|
|
for xml path('')
|
|
), 1, 1, '');
|
|
|
|
if isnull(ltrim(rtrim(@INTEZMENYEK)), '') = '' throw 50001, N'Nem található intézmény ID', 1;
|
|
print concat('Intézmény ID-k: ', @INTEZMENYEK);
|
|
|
|
|
|
|
|
-- simple recovery bekapcsolása
|
|
set @SQL = 'ALTER DATABASE ' + db_name() + ' SET RECOVERY SIMPLE';
|
|
if exists(select * from sys.databases where database_id = db_id() and recovery_model_desc <> 'SIMPLE') exec(@SQL);
|
|
|
|
|
|
|
|
-- triggerek letiltása ------------------------------------------------------------------------------------------------
|
|
set @SQL = '';
|
|
select @SQL += 'DISABLE TRIGGER ' + tr.name + ' ON ' + s.name + '.' + t.name + ';' + char(13)
|
|
from sys.triggers tr
|
|
inner join sys.tables t on t.object_id = tr.parent_id
|
|
inner join sys.schemas s on s.schema_id = t.schema_id
|
|
where tr.is_disabled = 0;
|
|
|
|
exec(@SQL);
|
|
|
|
|
|
|
|
-- foreign key-ek letiltása -------------------------------------------------------------------------------------------
|
|
set @SQL = '';
|
|
select @SQL += 'ALTER TABLE ' + s.name + '.' + t.name + ' NOCHECK CONSTRAINT ' + fk.name + ';' + char(13)
|
|
from sys.foreign_keys fk
|
|
inner join sys.tables t on t.object_id = fk.parent_object_id
|
|
inner join sys.schemas s on s.schema_id = t.schema_id
|
|
where fk.is_not_trusted = 0 or is_disabled = 0;
|
|
|
|
exec(@SQL);
|
|
|
|
|
|
|
|
-- nonclustered indexek törlése (opcionális) --------------------------------------------------------------------------
|
|
set @SQL = '';
|
|
select @SQL += 'DROP INDEX ' + i.name + ' ON [' + s.name + '].[' + t.name + '];' + char(13)
|
|
from sys.indexes i
|
|
inner join sys.tables t on t.object_id = i.object_id
|
|
inner join sys.schemas s on s.schema_id = t.schema_id
|
|
where i.type_desc = 'NONCLUSTERED'
|
|
and not exists(select * from sys.key_constraints pk where pk.type in ('PK', 'UQ') and pk.unique_index_id = i.index_id)
|
|
and i.is_unique = 0;
|
|
|
|
exec(@SQL);
|
|
|
|
|
|
|
|
-- intézmény adatok törlése -------------------------------------------------------------------------------------------
|
|
set xact_abort on;
|
|
set nocount on;
|
|
declare @object_id int = 0;
|
|
declare @DROP nvarchar(max), @INTO nvarchar(max), @TRUNCATE nvarchar(max), @INSERT nvarchar(max), @ADD nvarchar(max);
|
|
|
|
while (1 = 1)
|
|
begin
|
|
|
|
select top 1
|
|
@object_id = t.object_id,
|
|
@DROP = '',
|
|
@INTO = '',
|
|
@TRUNCATE = '',
|
|
@INSERT = '',
|
|
@ADD = ''
|
|
from sys.tables t
|
|
inner join sys.schemas s on s.schema_id = t.schema_id
|
|
inner join sys.columns c on c.object_id = t.object_id
|
|
where 1 = 1
|
|
and t.object_id > @object_id
|
|
and c.name in ('C_INTEZMENYID', 'C_ALINTEZMENYID')
|
|
and s.name not in ('auditlog', 'core', 'dev', 'HangFire')
|
|
and t.name not in ('XmlAudit')
|
|
order by t.object_id;
|
|
|
|
|
|
if @@rowcount = 0 break;
|
|
|
|
|
|
select
|
|
@DROP += 'ALTER TABLE ' + p.parent_object_name + ' DROP CONSTRAINT ' + fk.name + ';' + char(13),
|
|
@INTO = 'SELECT * INTO ' + t_columns.current_object_name + '_temp FROM ' + t_columns.current_object_name + ' WHERE ' + c.name + ' IN (' + @INTEZMENYEK + ');' + char(13),
|
|
@TRUNCATE = 'TRUNCATE TABLE ' + t_columns.current_object_name + ';' + char(13),
|
|
@INSERT = case when t_columns.has_identity = 1 then 'SET IDENTITY_INSERT ' + t_columns.current_object_name + ' ON;' + char(13) else '' end +
|
|
'INSERT INTO ' + t_columns.current_object_name + '(' + t_columns.column_list + ') ' +
|
|
'SELECT ' + t_columns.column_list + ' FROM ' + t_columns.current_object_name + '_temp;' + char(13) +
|
|
'DROP TABLE ' + t_columns.current_object_name + '_temp;' + char(13) +
|
|
case when t_columns.has_identity = 1 then 'SET IDENTITY_INSERT ' + t_columns.current_object_name + ' OFF;' + char(13) else '' end,
|
|
@ADD += 'ALTER TABLE ' + p.parent_object_name + ' WITH NOCHECK ADD CONSTRAINT ' + fk.name + ' ' +
|
|
'FOREIGN KEY (' + p_columns.column_list + ') REFERENCES ' + r.referenced_object_name + '(' + r_columns.column_list + ');' + char(13) +
|
|
'ALTER TABLE ' + p.parent_object_name + ' NOCHECK CONSTRAINT ' + fk.name + ';' + char(13)
|
|
from sys.tables t
|
|
inner join sys.schemas s on s.schema_id = t.schema_id
|
|
inner join sys.columns c on c.object_id = t.object_id
|
|
left join (
|
|
sys.foreign_keys fk
|
|
inner join sys.tables pt on pt.object_id = fk.parent_object_id
|
|
inner join sys.schemas ps on ps.schema_id = pt.schema_id
|
|
inner join sys.tables rt on rt.object_id = fk.referenced_object_id
|
|
inner join sys.schemas rs on rs.schema_id = rt.schema_id
|
|
) on fk.referenced_object_id = t.object_id
|
|
outer apply (select ps.name + '.' + pt.name as parent_object_name) p
|
|
outer apply (select rs.name + '.' + rt.name as referenced_object_name) r
|
|
outer apply (
|
|
select stuff((
|
|
select ',' + c.name
|
|
from sys.columns c
|
|
where c.object_id = t.object_id
|
|
and c.is_computed = 0
|
|
order by c.column_id
|
|
for xml path('')
|
|
), 1, 1, '') as column_list,
|
|
case when exists(select * from sys.columns c where c.object_id = t.object_id and c.is_identity = 1) then 1 else 0 end as has_identity,
|
|
s.name + '.' + t.name as current_object_name
|
|
) t_columns
|
|
outer apply (
|
|
select stuff((
|
|
select ',' + c.name
|
|
from sys.columns c
|
|
inner join sys.foreign_key_columns fkc on fkc.parent_column_id = c.column_id and fkc.parent_object_id = c.object_id
|
|
where fkc.constraint_object_id = fk.object_id
|
|
order by fkc.constraint_column_id
|
|
for xml path('')
|
|
), 1, 1, '') as column_list
|
|
) p_columns
|
|
outer apply (
|
|
select stuff((
|
|
select ',' + c.name
|
|
from sys.columns c
|
|
inner join sys.foreign_key_columns fkc on fkc.referenced_column_id = c.column_id and fkc.referenced_object_id = c.object_id
|
|
where fkc.constraint_object_id = fk.object_id
|
|
order by fkc.constraint_column_id
|
|
for xml path('')
|
|
), 1, 1, '') as column_list
|
|
) r_columns
|
|
where t.object_id = @object_id
|
|
and c.name in ('C_INTEZMENYID', 'C_ALINTEZMENYID');
|
|
|
|
|
|
|
|
set @SQL = CONCAT(
|
|
'BEGIN TRANSACTION;' + char(13),
|
|
@DROP,
|
|
@INTO,
|
|
@TRUNCATE,
|
|
@INSERT,
|
|
@ADD,
|
|
'COMMIT TRANSACTION;' + char(13),
|
|
'CHECKPOINT;'
|
|
);
|
|
begin try
|
|
exec(@SQL);
|
|
end try
|
|
begin catch
|
|
print @SQL;
|
|
throw;
|
|
end catch
|
|
|
|
end
|
|
|
|
-- speciális táblák
|
|
-- T_ENTITYATTRIBUTEHISTORY
|
|
ALTER TABLE dbo.T_ENTITYBLOBVALUES DROP CONSTRAINT IF EXISTS FK_EntityBLOBValues;
|
|
|
|
BEGIN TRANSACTION;
|
|
SELECT *
|
|
INTO dbo.T_ENTITYATTRIBUTEHISTORY_temp
|
|
FROM dbo.T_ENTITYATTRIBUTEHISTORY eah
|
|
WHERE EXISTS(SELECT * FROM dbo.T_ENTITYHISTORY eh WHERE eh.ID = eah.C_ENTITYHISTORYID);
|
|
|
|
|
|
TRUNCATE TABLE dbo.T_ENTITYATTRIBUTEHISTORY;
|
|
|
|
SET IDENTITY_INSERT dbo.T_ENTITYATTRIBUTEHISTORY ON;
|
|
INSERT INTO dbo.T_ENTITYATTRIBUTEHISTORY(ID, C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
|
|
SELECT ID, C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID FROM dbo.T_ENTITYATTRIBUTEHISTORY_temp
|
|
SET IDENTITY_INSERT dbo.T_ENTITYATTRIBUTEHISTORY OFF;
|
|
|
|
DROP TABLE dbo.T_ENTITYATTRIBUTEHISTORY_temp;
|
|
COMMIT TRANSACTION;
|
|
|
|
|
|
|
|
-- foreign key-ek visszakapcsolása (opcionális) -----------------------------------------------------------------------
|
|
set @SQL = '';
|
|
select @SQL += 'ALTER TABLE ' + s.name + '.' + t.name + ' CHECK CONSTRAINT ' + fk.name + ';' + char(13)
|
|
from sys.foreign_keys fk
|
|
inner join sys.tables t on t.object_id = fk.parent_object_id
|
|
inner join sys.schemas s on s.schema_id = t.schema_id
|
|
where fk.is_not_trusted = 1 or is_disabled = 1;
|
|
|
|
exec(@SQL);
|
|
go
|
|
|
|
|
|
-- shrinkelés ---------------------------------------------------------------------------------------------------------
|
|
DBCC SHRINKDATABASE(0);
|
|
CHECKPOINT;
|
|
DBCC SHRINKDATABASE(0);
|