Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-05-30
4
Medium Priority
?
344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 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