1

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?

Tim Stone
  • 19,119
  • 6
  • 56
  • 66
Frank Schwieterman
  • 24,142
  • 15
  • 92
  • 130

1 Answers1

2

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 ?'
Frank Schwieterman
  • 24,142
  • 15
  • 92
  • 130
Ben Laan
  • 2,607
  • 3
  • 29
  • 30