Link to home
Create AccountLog in
Avatar of chrislindsay
chrislindsayFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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?
Thanks.
Avatar of mherchl
mherchl
Flag of Slovakia image

TRUNCATE TABLE table_name
Avatar of drequinox
drequinox

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.
Avatar of chrislindsay

ASKER

Hi Guys,
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?
Thanks
Hi,

USE VPMA_cert

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
GO

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
Hi Gents,
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.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Jagdish Devaku
Jagdish Devaku

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account