-3

I would like to create a db reset script.

1: Drop all FK constraints

2: Truncate all tables (I can manage this, but help appreciated)

3: Recreate all FK constraints.

How can I generate step 1 and 3 from the current state of the db?

apaul
  • 16,092
  • 8
  • 47
  • 82
g.pickardou
  • 32,346
  • 36
  • 123
  • 268
  • 2
    Why not disable them? http://stackoverflow.com/questions/737115/turn-off-constraints-temporarily-ms-sql – pritaeas Nov 11 '13 at 08:45
  • 1
    There is a way. Look at all sys views, they have the information you need. But are you asking us to write that for you? – Szymon Nov 11 '13 at 08:47
  • pritaeas: Thx good idea. Szymon: You are right. The question is actually misleading. I know that this metainfo accessible via system tables or schema views, I am just do not want to actually reinvent them if anybody have some utility for it, although I do not want to write it for me by others from scratch. – g.pickardou Nov 11 '13 at 10:24

1 Answers1

2

First you need to look up all foreign keys for the table in questions (or a list of tables from the DB first), then disable all foreign keys -by their name-, do the truncate and re-enable keys. You might need dynamic sql throughout this process.
Technically, you can put this fairly simple script/SP together in minutes after 2-3 mins googling for the solution.

If it's about mass-destroying all data, I'd also consider dropping the DB (or tables in question) and re-creating from a .sql, as that probably is faster.

Community
  • 1
  • 1