Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Dynamic 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 di…
Screencast - Getting to Know the Pipeline
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

571 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