Given a SQL Express database base file (.MDF) how can I wipe/clear/reset the schema and/or data?

Question

Whats options do I have to clear the schema and data from a MDF file? What options to delete all the data?

To reset a databases schema, it seems I need to copy a file from a backup of the database when it was empty. I was wondering if there was a simpler or more efficient way.

To clear all data, it seems I'd need to write a script. The script would disable constraints, then drop all rows from each table before turning back on constraints. This is straightforward but does require I discover/track what tables exist in the database. Maybe its not sufficient or there is an easier approach?

Answer 1

I'm not sure what the point is of 'clearing the schema' - surely a new database already has a 'clear' schema.. BUT, you can create a new database in code via the following T-SQL:

USE Master
CREATE DATABASE NewDb (NAME=NewDbFile, FILENAME= '<filepath>')

If you need a file (an MDF) you can then detach the database too with sp_detach_db and you can then move it as required from the location specified above:

EXEC sp_detach_db NewDb

To clear the data you can use sp_msforeachtable with a truncation command - it is a non-logged operation, and does not check constraints, nor foreign keys - however, it cannot be rolled back!

EXEC sp_msforeachtable 'TRUNCATE TABLE ?'