4

I'm searching for a generic SQL query that replaces the following procedures:

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------  

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

Example:

The following query drops foreign keys for specific table. Can it be converted to a generic one? (Please don't suggest dropping the whole database as I don't have permissions.)

SELECT 
'ALTER TABLE ' + OBJECT_NAME(parent_object_id) + 
' DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

Question:

How can I empty a database without dropping it?

Edit: Wait Wait, it's not a duplication question! The other question was about emptying rows ( or data ONLY ) keeping relations and tables. I'm trying to drop all data, tables & relations without dropping the database itself!

Leigh
  • 28,765
  • 10
  • 55
  • 103
Ahmed Ghoneim
  • 6,834
  • 9
  • 49
  • 79
  • What is the purpose of doing this? – mmmmmm Dec 23 '12 at 13:45
  • Emptying database without dropping it, Godaddy Rules! – Ahmed Ghoneim Dec 23 '12 at 13:46
  • Wait Wait, It's not a duplication one! other question was about emptying rows ( data ), I'm here for dropping database without really dropping it! – Ahmed Ghoneim Dec 23 '12 at 13:55
  • What do you mean by "**Generic**"? – RBarryYoung Dec 23 '12 at 15:02
  • Do you want to guarantee that the database is in a known state, i.e. no user objects (tables, SPs, UDFs, ...), all options set to specific values, all security reset, ... ? – HABO Dec 23 '12 at 16:03
  • Generic, run on any database connection string, emptying it without providing tables names or relations names – Ahmed Ghoneim Dec 23 '12 at 19:45
  • 2
    I would use GoDaddy's backup/restore to repeatedly refresh a database back to an empty state, ie Create a new empty db once, back it up, and then periodically restore to a known (and empty) state. – nathan_jr Dec 24 '12 at 02:44
  • Thanks @NathanSkerl, the only defect in your suggestion is that GoDaddy's database restore take up-to 15 minutes and a minimum of 3 minutes, I went with that for 3 days but it has a really bad performance wise! – Ahmed Ghoneim Jan 01 '13 at 15:59

1 Answers1

1

Check out this answer How to drop all Foreign Key constraints in all tables?

This will drop your foreign keys from all tables, you can either add a drop table into the loop, or create a new loop afterwards to drop the tables

Community
  • 1
  • 1
Macros
  • 7,099
  • 2
  • 39
  • 61