How to remove all data from all tables in a database?
-
Hi all,
We had a replicated database in a server, and we want to truncate over 3000 tables, how to do it?
Thank you.
-
Is that all of the tables in the database? If so and depending on how much data is in each table, you may consider rebuilding your database from scratch. Do you have a backup of an empty database? If this is something you are going to have to do from time to time, I would create a blank schema database and get a backup of it. That way, the next time you do this, you have an empty schema database you can restore to.
-
You can write a script to produce the a script to execute.
Try somehting along the lines of this:
select
case
when xtype = 'P' then 'Truncate ['
end
+ o.name + ']
Go'
from sysobjects o
where xtype = 'P'and name <> 'dtproperties'
Then you can take the result set and execute it.
-JG -
In my script below replace P with U this will give you user objects and the P will give you stored procedures.
-JG -
did you try sp_msforeachtable with truncate table statement? Additionally, in the previous post of JG the type should be U, not P
Regards,Yelena Varsha
-
You can also try the sp_MSforeachtable in master. I think i'ts undocumented but I've seen lots of posts that use it.
-
you can't truncate a table that has a foreign key reference.
also, if you delete from the tables out of order, you'll end up with errors as well.
another thing to consider is that there will always be some tables, like lookup tables, that you may not want to trucate/delete the contents of.
here's a suggested solution if you wanted to get you started:
nocount on
declare @level tinyint
set @level = 0
create table #tables (
id int not null primary key clustered,
TableName varchar(255) not null,
Level tinyint not null)
insert into #tables (id, TableName, Level)
select id, '[' + user_name(uid) + '].[' + rtrim(name) + ']' as TableName, 0
from sysobjects where xtype = 'U' and status > 0
while @@rowcount > 0 begin
set @level = @level + 1
update rt set Level = @level
from #tables rt
inner join sysreferences fk on fk.rkeyid = rt.id
inner join #tables ft on ft.id = fk.fkeyid
where ft.Level = @level - 1
end
print 'USE ' + DB_NAME() + '
'
select 'TRUNCATE TABLE ' + TableName from #tables where level = 0
select 'DELETE ' + TableName from #tables where level > 0 order by level
drop table #tables
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi there,
being a GUI sort of person
, I would probably use the DTS export wizard in EM, select the Copy Objects and Data between SQLServer databases option, and uncheck the option to copy data. This should create an empty database.David
If it ain't broke, don't fix it...
-
Hi, just a thought. If no data is to be left at all you might consider scripting the database in Enterprise Manager.
Select the database in question, from Tools select Generate SQL Script. There are bags of criteria to choose from and selecting OK will cause the script to be written.
Then drop the database and run your script to recreate it.
-
use Enterprise Manager
Select database Server
select database whis is required to clean
right click on database
select all tasks
click on Generate SQL Script
create new database using the above script
bondada
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply