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.
Thanks.
Microsoft SQL Server 2005
Last Comment
Jagdish Devaku
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.
EXEC sp_MSForEachTable 'Truncate Table ?'
this will truncate all tables without having to run the statement for each table individually.
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
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
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
ASKER
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
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
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.