Does anyone have a SQL procedure to delete ALL records in a DB ?

Question

Post reply

Does anyone have a SQL procedure to delete ALL records in a DB ?

isuckatsql

SSChampion

Points: 10381
More actions
September 13, 2010 at 8:23 am

#387772

I have a test SQL DB(approx 5 GB) and would like to delete all the records from within the DB.
Does anyone have a SQL Procedure to do this?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Abhijit More SSCertifiable Points: 6897 More actions · Answer 1

What is the case scenario?

Why do you want to delete records from all table?

Abhijit - http://abhijitmore.wordpress.com

nzrdb6 Say Hey Kid Points: 695 More actions · Answer 2

nzrdb6

Say Hey Kid

Points: 695

September 13, 2010 at 8:42 am

#1220099

script out the schema of the DB and drop the DB

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Use the system tables to generate DELETE FROM statements for all tables. Run repeatedly until there's nothing more to delete (assuming you have foreign keys. If not, one run will be sufficient)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 4

Running this will create a script to do it.

It works in 2008 but yes, I know it is deprecated code - I presume the OP just wants something to use now.

BE CAREFUL

SET NOCOUNT ON

SELECT '-- WARNING - MAKE SURE YOU HAVE SELECTED THE CORRECT DATABASE'

SELECT '-- COPY THE CODE IN THIS RESULTS WINDOW INTO A QUERY WINDOW AND RUN IT'

-- Tables with no foreign key constraints acting on them can be truncated

SELECT

'TRUNCATE TABLE ['+name+']'

FROM

sysobjects

WHERE

xtype = 'U'

AND id NOT IN (

SELECT

referenced_object_id

FROM

sys.foreign_key_columns

)

ORDER BY

name

-- Tables with foreign key constraints acting on them must be deleted

SELECT

'DELETE FROM ['+name+']'

FROM

sysobjects

WHERE

xtype = 'U'

AND id IN (

SELECT

referenced_object_id

FROM

sys.foreign_key_columns

.

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 5

Sorry, previous post lost the last bit.

-- Tables with foreign key constraints acting on them must be deleted

SELECT

'DELETE FROM ['+name+']'

FROM

sysobjects

WHERE

xtype = 'U'

AND name NOT IN ('ta_Deployment_Load','ta_Deployment_Audit')

AND id IN (

SELECT

referenced_object_id

FROM

sys.foreign_key_columns

)

ORDER BY

name

.

Lowell SSC Guru Points: 323518 More actions · Answer 6

another version, this with some exclusions, since when someone says "del3ete everything" , they never think it through,a nd afterwards the say "well i mean everything except the lookup tables, and .....

my version generates the list in foreign key hierarchy order, so there's no errors.

nocount on

CREATE TABLE #Skipme(TableName varchar(255))

INSERT INTO #Skipme

SELECT 'tbCity' UNION ALL

SELECT 'tbState' UNION ALL

SELECT 'tbCounty' UNION ALL

SELECT 'OtherLookupTables'

INSERT INTO #Skipme

SELECT name from sys.objects

where LEFT(name,2) = 'LU'

OR LEFT(name,2) = 'TB'

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

And TableName Not In (SELECT TableName from #Skipme)

select 'DELETE ' + TableName

from #tables

where level > 0

And TableName Not In (SELECT TableName from #Skipme)

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!

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

nzrdb6 (9/13/2010)
script out the schema of the DB and drop the DB

WHAT? I'm at a loss for words for expressing how bad an idea this is!

Would you remove a splinter in a finger by amputating the hand?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 8

Which is why I said 'BE CAREFUL'.

Clearly using the script in the wrong circumstances could get you fired!

.

isuckatsql SSChampion Points: 10381 More actions · Answer 9

Abhijit More (9/13/2010)
What is the case scenario?
Why do you want to delete records from all table?

It was being used as a test DB and i would like to get rid of the 200k test records and create a new production DB and test DB from scratch.

Thanks for all the responses!