Soms kan het nodig zijn om alle records uit een SQL Server database te verwijderen.
Je kunt dan natuurlijk de betreffende database verwijderen en een nieuwe ‘schone’ database terugplaatsen.
een andere mogelijkheid is om een script uit te voeren om alle records uit de SQL database te verwijderen.
Records verwijderen uit een SQL Server database
Je kunt in een relationele database niet zomaar alle records verwijderen. Records zijn vaak aan elkaar gelinkt d.m.v. constraints. SQL Server zal daardoor het verwijderen van records niet zomaar toestaan.
Om de records te kunnen verwijderen worden daarom eerst de constraints en triggers uitgeschakeld. Daarna kunnen alle records verwijderd worden en als laatste worden de constraints en triggers weer geactiveerd.
Records verwijderen via een stored procedure
Om de records te kunnen verwijderen maak je eerst een stored procedure aan via SQL Server Management Studio.
Deze stored procedure maakt gebruik van een ongedocumenteerde functie sp_MSForEachTable.
CREATE PROCEDURE sp_DeleteAllData
AS
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘ALTER TABLE ? DISABLE TRIGGER ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘ALTER TABLE ? ENABLE TRIGGER ALL’
EXEC sp_MSFOREACHTABLE ‘SELECT * FROM ?’
GO
Nadat deze stored procedure is aangemaakt, kan deze worden uitgevoerd met het volgende commando:
EXEC sp_DeleteAllData
De procedure zal dan in alle tabellen van de betreffende SQL Server database de records verwijderen en daarna een overzicht maken van alle overgebleven records in de tabellen.
Afhankelijk van het aantal tabellen en het totaal aantal te verwijderen records, kan dit script een ruime tijd in beslag nemen.
Als het verwijderen van de records goed is uitgevoerd, moeten alle tabellen natuurlijk leeg zijn.