September 25, 2007 at 8:09 am
We have a test database where we need to clear out all of the data from the tables (excluding, of course, system tables), but retaining the views, stored procedures etc. I am wondering if there is an easy way to do this, or if anyone knows of any third party tools which will achieve this.
Many thanks in advance
Bruce
September 25, 2007 at 8:16 am
I would suggest to script out all the database objects, constraints, permissions,users etc.
When drop and re-create the database using the scripts.
This approach has as an advantage over truncating all tables, that all Identity columns will be automatically reset.
Markus
[font="Verdana"]Markus Bohse[/font]
September 25, 2007 at 9:29 am
you should script the deletes and truncates manually;
when people say they want to delete the data, they are really saying just certain data...they typically don't want to dete teh data that are setup tables, like tbStates,TbCities,TbCounties,tbStatus, etc.
here's a tool to help you do that, but You'll want to trim the lsit to eliminate setup tables:
this makes the statements in foreign key hierarchy order, so they won't fail due to a foreign key.
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
September 25, 2007 at 10:01 am
How about scripting out TRUNCATE statements:
select 'truncate table ' + name
from sysobjects
where xtype = 'U'
Greg
Greg
September 25, 2007 at 10:15 am
I like Greg's solution, though I might use Information_schema views instead. Then remove the tables you want to keep.
September 25, 2007 at 10:37 pm
I do a bit of this myself, and prefer to put a cursor around the information_schema.tables data.
Try a bit of this action:
select distinct table_name
from information_schema.tables
where table_type = 'BASE TABLE' and left(table_name,3) not in ('sys','dtp')
- note the above is ignoring all sys tables, and the dtproperties table. It should bring back the tables you'll want to truncate.
Put that into a cursor, and use the sp_SqlExec command, something like this.
declare @TableName varchar(255)
declare @SQLCommand varchar(500)
declare AllTables cursor for
select distinct table_name
from information_schema.tables
where table_type = 'BASE TABLE' and left(table_name,3) not in ('sys','dtp')
open AllTables
Fetch next from AllTables into @TableName
While @@Fetch_Status = 0
Begin
set @SQLCommand = ''
set @SQLCommand = 'truncate table '+@TableName
print @SQLCommand
-- exec sp_SqlExec @SQLCommand
Fetch next from AllTables into @TableName
end
close AllTables
Deallocate AllTables
Obviously when your ready to run it, comment out the print command and put in the exec command... I think that should work for you. You'll need to run it in the database of your choice, or put the database name in front of the information_schema, like this
database.information_schema.tables
Cheers, hope this helps.
September 26, 2007 at 1:19 am
Many thanks to all who replied. Has given me some ideas on the way to proceed.
Bruce
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply