SQL n00b trying to empty data from a database
-
Hi All,
We have an SQL database that is filled by a Cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
Thanks
-
First thing, make sure you have a working and current database backup.
Then the best thing I can think of would be to script out all the tables. Make sure "Generate the DROP..." option is selected on the "Formatting" tab of the scripting options dialog. Copy the resulting script into Query Analyzer and run it. Bye Bye data.
--------------------
Colt 45 - the original point and click interface
-
MAKE SURE YOU BACKED UP FIRST :
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
-
Truncate table won't work if there are Foreign Keys defined.
--------------------
Colt 45 - the original point and click interface -
Forgot that one... can always use delete in simple mode so that the log doesn't grow of of proportion...
-
this will create the delete/truncate statements to be used in Foreign key hierarchy order, so it never fails: note that it screws with every table, so any lookup tables or similar reference tables will be doinked as well; you might need to add some exclusions for tables in a further analysis.
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!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply