delete all content i all tabels in one database
-
Hi
Is it possible to delete all content in all/several tables in one databases at the same time.
I just want to empty them, not delete the tables from the server.
The reason for this is that the database is widley used and it has now become huge. I update this datbase each day, and several tabels two times a day.
I want to start with a blank database to see how much space it allocates.
Regards
DJ
-
Instead of deleting all/several tables, I would rather script all db objects into one file, run this under a different db name against another server and see what size I get there.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Be very sure that you are in the correct database when you run this.
DECLARE @sObjectName NVARCHAR(128)
--Loop through all table objects.
SET@sObjectName = ''
WHILE@sObjectName IS NOT NULL
BEGIN
SELECT@sObjectName = MIN ( Name )
FROMSysObjects
WHEREType='U' AND NAME > @sObjectName
IF @sObjectName IS NOT NULL
BEGIN
TRUNCATE TABLE @sObjectName
PRINT '....Truncating table ' + @sObjectName
END
END
-
However, still you won't get reliable results on how many space your db allocates. TRUNCATE does not compact the database, so that result is skewed.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes u can delete all the tables and empty the logs ,
But as frank said most reliable and easy way is to recreate database from the begining.
In that way you have the confidence that the database is new. don't you?
My Blog:
http://dineshasanka.spaces.live.com/ -
you may want to have a look at sp_create_removable
hth
* Noel -
I think that due to foreign key constraints, a cursor is not the right solution.
i would recommend adapting the Hierarchy2 script from this site.
http://www.sqlservercentral.com/scripts/contributions/759.asp
it returns all tables in the hierachial (?sp?) order of the foreign keys, so you can then delete the data in the tables in the proper order.
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! -
The script Lowell referenced appears flawed. It gave me incorrect results.
This script creates the truncate/delete commands in order with respect to foreign keys as Dan requested, but I think Frank's answer (script to new DB) was the best idea.
The script only generates the commands to do the deletion. I don't like putting EXEC in these things and turning them loose without checking what they'll do.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply