Solved

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

Posted on 2013-05-30
4
340 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 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 27

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

752 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