Generic SQL Query for Empty Database

Question

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!

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
– Ahmed Ghoneim, Commented Dec 23, 2012 at 13:55
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
– nathan_jr, Commented Dec 24, 2012 at 2:44

Community · Accepted Answer · 2017-05-23 10:24:45Z

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

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Dec 23, 2012 at 13:58

Macros

7,1092 gold badges42 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generic SQL Query for Empty Database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related