Solved

How to delete all table data ane make the schemas alone.

Posted on 2013-05-30
4
337 Views
Last Modified: 2013-05-30
I've a DB which has thousands of tables. I would like to delete all table content and to have the schema alone. How to achieve it? Please do suggest.
0
Comment
Question by:Easwaran Paramasivam
4 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39207222
would be a lot easier to script your database, drop it, and use the script to recreate the schema only.
0
 
LVL 7

Assisted Solution

by:aplusexpert
aplusexpert earned 350 total points
ID: 39207565
Hii EaswaranP,

Please try this. Its working.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Open in new window


Thanks.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 150 total points
ID: 39207768
The best way is to script out the database structure and then drop the database, create it back as empty after which you will run the script created.

In Management Studio right click on the database name > Tasks > Generate Scripts > click Next > option: Choose entire database and all database options > Next > option: Save scripts to a specific location > option: Save to file > option: Single file - set a name and a location > click Advanced > Make sure you set to true all the option that you need from the Table/View bottom part, for sure Check Constraints and Triggers > click OK > click Next > click Next > script will be generated > click Ok.

You will find the script at the location you set to be saved. You now can drop the database and create a new one empty with the same name, then open the script and execute it.
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39209989
Thanks.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now