June 19, 2009 at 8:05 am
Hi,
Does anyone know about a way/utitility to delete all data from all tables within a schema?
And also all tables from a database?
Thanks,
Suhas.
June 19, 2009 at 8:12 am
you can write a query to delete all tables in a schema.
Also wouldn't it be good to drop the database if you do not want tables to be there?
Question is why do you want to drop all tables instead of dropping database?
June 19, 2009 at 8:17 am
ps (6/19/2009)
you can write a query to delete all tables in a schema.Also wouldn't it be good to drop the database if you do not want tables to be there?
Question is why do you want to drop all tables instead of dropping database?
or RESTORING the database to the point before the tables existed as well...much simpler and faster.
Lowell
June 19, 2009 at 8:42 am
Except for third party databases, all our databases are built from source, so we can drop the database and recreate it pretty much by pushing a button.
However, you could look at using sp_msforeachtable as a way to walk through every table to delete the data. The problem with that is, you're likely to run into referential constraint issues. So then, you need to drop all the FK's prior to running the delete, but in order to drop all the FK's, you also need to have them all scripted out so that you can recreate them...
In other words, it's much easier to recreate a blank database than it is to blank a database with data.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 19, 2009 at 8:50 am
here's a script that creates the delete/truncate commands for all tables in the correct foreign key hierarchy order....
the issue I've always found is that often you have lookup tables, like status, counties,cities and such that should not be deleted in the first place, but this might 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
June 19, 2009 at 9:03 am
Thanks. I'll try it out.
Dropping a database and creating a new one is an option. But, I was trying to avoid dropping the procedures and functions written for the database.
June 19, 2009 at 9:13 am
suhas.wadadekar (6/19/2009)
Thanks. I'll try it out.Dropping a database and creating a new one is an option. But, I was trying to avoid dropping the procedures and functions written for the database.
True, but if you script out the entire database then recreating it easy.
If you do run the deletes, keep an eye on your transaction log.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply