Start Free Trial
removing data from sql tables
I would like to remove all test data and reset the autonumbers from a series of tables within an SQL database. The DROP command seems to delete the table. Is there any other way of keeping the structure and just removind and resetting?
Microsoft SQL Server 2005
8/22/2022 - Mon
TRUNCATE TABLE table_name
trucnate is good. if you need complete database and truncate all table try considering
EXEC sp_MSForEachTable 'Truncate Table ?'
this will truncate all tables without having to run the statement for each table individually.
I am obtaining foreign key constraints using the truncate command. Using EXEC sp_MSForEachTable 'Truncate Table ?' my database is VPMA_cert and I want to reset all tables within this.
Where do I insert this database name in the expression above?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
I have an error message with the scrip above,
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'dbo.aspnet_Applications' because it is being referenced by a FOREIGN KEY constraint.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Plans and Pricing
Certified Expert Program
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent