July 17, 2008 at 11:12 am
hello,
I have a database with 100 user defined tables.Can you please let me know how to delete data in all 100 tables by using a single query or SP.
thanks
kumar
July 17, 2008 at 11:34 am
- with the least overhead you could generate and execute truncate statements for every table. (truncate will only work if there are no FKs pointing to the table)
- or you could generate and execute 'delete from ...' statements but that will generate log-file overhead.
- If these are the only tables in the database you could use the undocumented sproc sp_msforeachtable
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 17, 2008 at 11:37 am
CREATE PROCEDURE USP_BOOM_DATA
AS
DELETE FROM TABLE_NAME1
DELETE FROM TABLE_NAME2
.
.
.
.
DELETE TABLE_NAME100
after that you can call this procedure
EXEC USP_BOOM_DATA
But if you have the table with Primary Key and it's sure that you have every table with primary key add also this code after delete clause like this:
DELETE FROM TABLE_NAME50
DBCC CHECKIDENT('TABLE_NAME50', RESEED, 0)
and so on
it will help you to save the time but first you should create the Procedure
Cheers!
Dugi
:w00t::w00t::w00t:
July 17, 2008 at 11:40 am
...And also shrinking DB after these operation is very wellcome if you use DELETE CLAUSE but you can use also TRUNCATE TABLE TABLE_NAME1..100
TRUNCATE is faster than DELETE
July 17, 2008 at 11:50 am
...ok I test right now with the TRUNCATE ( I forget it at my first post )
CREATE PROCEDURE USP_BOOM_DATA
AS
TRUNCATE TABLE TABLE_NAME1
.
.
.
.
TRUNCATE TABLE TABLE_NAME100
then
EXEC USP_BOOM_DATA
that's it!!!
But I'm sure that you can have problem with Primary Keys and Foreign Keys...for that reason you should use DELETE clause!
:hehe::w00t::hehe::w00t::hehe:
July 17, 2008 at 12:44 pm
The TRUNCATE command maintains the structure and its columns, constraints, indexes etc. While if there are Foreign Key constraints, use DELETE. This will also help you retain any identity column seeds. Delete will reset the seed values.
Truncate is a non logged operation, While DELETE is a logged operation.Delete has many options to play with:
Now to you original Question:
This Query will give you the TRUNCATE/DELETE statements for each table.
Replace the TRUNCATE TABLE with your OWN DELETE FROM Clause.
USE DATABASENAME
SELECT 'TRUNCATE TABLE '+Table_name from information_Schema.tables where table_type='BASE TABLE'
Or Create a SP as already specified with all the table names.
to get all table names:
select table_name from information_Schema.tables where table_type='BASE TABLE' ORder by Table_name
Maninder
www.dbanation.com
July 17, 2008 at 2:09 pm
Hi Kumar,
please try below query:
Use Database name
declare @text varchar(40)
declare trig cursor for select name from sysobjects where xtype='u' and name not like 'dtproperties'
open trig
fetch next from trig into @text
while(@@fetch_status=0)
begin
create table trig(name varchar(40))
insert into trig values(@text)
select * from trig
print'delete from '+@text
exec ('delete from '+@text)
drop table trig
fetch next from trig into @text
end
close trig
deallocate trig
Thank You.
Regards,
Raghavender Chavva
July 17, 2008 at 2:45 pm
ALZDBA (7/17/2008)
but that will generate log-file overhead.
set the database to bulk logged recovery mode before executing the delete statements
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2008 at 2:47 pm
You do not say if amongst these 100 user tables when created or later altered have been defined with
[ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
Bold type and underlining added for ease of reading only.
If they have been the Foreign key problem will be solved. If not and there is a minimal amount of referential integrity between these tables you might want to alter those few tables to Cascade on Delete.
For a further reference see BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/54ca1d10-5106-4340-bde4-480b83e7e813.htm
Sorry it is not a single SP solution, but it may result in allowing you to create one.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply