Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-05-30
4
Medium Priority
?
347 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 70

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 1400 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 27

Accepted Solution

by:
Zberteoc earned 600 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

885 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