Löschen des Datenbankinhalts |
  
|
Löschen des Datenbankinhalts |
|
Der folgende Skript löscht fast alle Daten aus der Datenbank und setzt diese wieder in den Auslieferungszustand zurück. Das löschen erfolgt dabei in einzelnen Schritten zu je 100 elementen. Falls der skript also auf grund eines timeouts abbricht, kann der vorgang einfach so oft neu gestartet werden, bis die Löschung vollständig ist.
Declare @string varchar(8000)
Declare @tableName nvarchar(500)
Declare cur cursor
for select name as tbname from sysobjects where id in(select parent_obj from sysobjects where xtype='tr')
open cur
fetch next from cur into @tableName
while @@fetch_status = 0
begin
set @string ='Alter table '+ @tableName + ' Disable trigger all'
exec (@string)
Fetch next from cur into @tableName
end
close cur
deallocate cur
DELETE FROM Konto_Bankdaten_MRU
UPDATE NummernPool_Bereich SET Nummer_Start=1, Nummer_Ende=9223372036854775807,Nummer_Aktuell=0
DELETE FROM NummernPool_Nummern
DELETE FROM Parameter WHERE NOT Parameter='DBTYPE' and NOT Parameter='SchemaVersion' and NOT Parameter='ConnectionDLL_AssemblyName' and NOT Parameter='ConnectionDLLVersion' and NOT Parameter='ConnectionDLLMinVersion'
DELETE FROM Parameter_NonReplica
DELETE FROM Server_Aktionen
Declare @Count bigint
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM Statistic_LagerVerlauf
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM Statistic_LagerVerlauf_Gesamt
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Adressen
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Adressen_Skonto
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Ansprechpartner
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Arbeitsplan
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Arbeitsplan_Beistelliste
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_ArtikelOptionen
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Eigenschaften
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Lagerstand
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Lieferanten
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Mindestbestand
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Paket
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Queue
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Vorgangsliste
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Warenbewegung
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikel_Zubehör
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_ArtikelOptionen
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Artikelzuordnung
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Auftrag
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Benutzer
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_ChangeTracking
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Einstellungen
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Fahrzeugdaten
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Fahrzeugdaten_Artikel
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Feldbezeichnungen
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Inventur
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Inventur_Artikel
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Kassen
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Kassenbuch
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Konto_Aufträge
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Konto_Kontostände
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Konto_Lastschriften
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Kontoauszüge
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_KontoOptionen
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Kundenrabatt
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Lager
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Lager_Artikelzuordnung
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Lagerbestand
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Lieferadressen
SET @Count = @@ROWCOUNT
END
DELETE FROM tbl_Profile WHERE NOT ID=0
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Protokoll
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_QuickSteps
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Rabatt
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_ServerAktionen
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Statistik_Artikel
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Textbausteine
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Warengruppen
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Zahlungsziel
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Zweigstellen
SET @Count = @@ROWCOUNT
END
SET @Count = 1
WHILE @Count > 0
BEGIN
DELETE TOP(100) FROM tbl_Nachrichten
SET @Count = @@ROWCOUNT
END
Declare cur cursor
for select name as tbname from sysobjects where id in(select parent_obj from sysobjects where xtype='tr')
open cur
fetch next from cur into @tableName
while @@fetch_status = 0
begin
set @string ='Alter table '+ @tableName + ' Enable trigger all'
exec (@string)
Fetch next from cur into @tableName
end
close cur
deallocate cur