Clear data in all tables - application problem

Question

Post reply

Clear data in all tables - application problem

Frank Rueff

SSC Enthusiast

Points: 155
More actions
April 27, 2005 at 10:37 am

#85968

This has to be done because they did not convert the application 10 years ago when they were supposed to. Of course our version is no longer supported and no one there knows anything about it. I need to bring the database over with just the tables and no data. DTS will not work because of some binding problem. Due to time constraints, I would like to restore the DB and then clear all the data out of the tables. I have not found any way to do this yet. Any ideas?

Thanks

Frank

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

if that database has no FK you could

sp_MSforeachtable 'Truncate table ? '

if there are FK -- Which btw should be the expected thing

you have to find the dependencies on the tables and delete from child to parent!

OR

you could simply script the objects out and run the script on a newly created DB

* Noel

Lowell SSC Guru Points: 323482 More actions · Answer 2

The foreign keys hierarchy can prevent you from just deleting data in the tables;

the script below creates a list of all the tables in hierarchy order, and creates the delete statemetns or truncate statements if the table had no dependancies;

you could change thist to use dynamic sql to drop them directly, instead of as output from the temp table:

set 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!

Blake Bishop SSC Rookie Points: 32 More actions · Answer 3

It sounds like all you need is the schema -- when you say you want all data removed, you do mean all data, correct? If so, you could write the database schema to a file using Enterprise Manager's "Generate SQL Script..." feature, move the script to your destination server, then create a new, empty database on the destination server, open the script in Query Analyzer and run it, and voila! You should have a working copy of an empty database.