Delete Rows from multiple tables

Question

Post reply

Delete Rows from multiple tables

Michael-760538

SSC Enthusiast

Points: 155
More actions
June 5, 2008 at 2:04 pm

#208162

I work with a database that we are trying to free up some memory. The database is 23 GB on a 25 GB Drive. All tables (around 5500) are all the same format 3 rows- TSTAMP, VALUE, STATUS. I want to delete all rows from prior to 2006 which I can do with this statement:
DELETE FROM rowname
WHERE (TSTAMP < '2007-01-01 00:00:00')
However I do not want to do this for every table it would take days. Is there a way to start at the first table run this query then goto the next run query....
Thanks for any help.
Out of memory.

Viewing 15 posts - 1 through 15 (of 22 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Do a search for this undocumented Microsoft stored procedure: sys.sp_MSforeachtable.

😎

chandra.pottipati SSC Veteran Points: 238 More actions · Answer 2

If you use this query you can get all tables in a database

select table_Name from information_schema.tables where table_Type = 'BASE TABLE'

Use a cursor / loop to loop thru the results and build the delete statement into a varchar variable (for ex @sql)

and use Exec(@Sql) inside the loop

Chandra

chandra.pottipati SSC Veteran Points: 238 More actions · Answer 3

this sounds like a more easier solution than what I proposed.

Michael-760538 SSC Enthusiast Points: 155 More actions · Answer 4

Sounds like I need to read about cursor / loops. I am also researching sys.sp_MSforeachtable.

Michael-760538 SSC Enthusiast Points: 155 More actions · Answer 5

So after looking around sp_MSforeachtable here is what I came up with:

exec sp_MSforeachtable

@command1 = 'DELETE FROM',

@whereand = '(TSTAMP < '2007-01-01 00:00:00')'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

One thing I will suggest (strongly), test it in a test DB first to ensure it does what you expect.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Or you could avoid the use of Cursors and Loops altogether:

Declare @sql Varchar(max);

Select @sql = ''

Select @sql = @sql + '

DELETE FROM [' + TABLE_CATALOG + '].[' + TABLE_NAME + ']

WHERE TSTAMP < ''2007-01-01 00:00:00'';'

From INFORMATION_SCHEMA.TABLES

EXEC(@sql) --Do it!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 8

Depending on the number of rows, you might also want to limit the batch size, meaning delete 1000 at a time.

Also, you're not freeing memory, you're freeing disk space.

Michael-760538 SSC Enthusiast Points: 155 More actions · Answer 9

Thanks for the help. I will be testing out different options on a backup server to see what works the best.

noeld SSC Guru Points: 96590 More actions · Answer 10

A word of caution either make sure you have frequent transaction log backups or enough log drive space.

😉

cheers.

* Noel

Michael-760538 SSC Enthusiast Points: 155 More actions · Answer 11

When executing query:

exec sp_MSforeachtable

@command1 = 'DELETE FROM',

@whereand = '(TSTAMP < '2007-01-01 00:00:00')'

I receive this error message:

Server: Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near '2007'.

Any ideas on the correct syntax to make this work?

noeld SSC Guru Points: 96590 More actions · Answer 12

Michael (6/11/2008)
When executing query:
exec sp_MSforeachtable
@command1 = 'DELETE FROM',
@whereand = '(TSTAMP < '2007-01-01 00:00:00')'
I receive this error message:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '2007'.
Any ideas on the correct syntax to make this work?

exec sp_MSforeachtable

@command1 = 'DELETE FROM ? ',

@whereand = '(TSTAMP < '2007-01-01 00:00:00')'

* Noel

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Also - since you're trying to put a single quote within the SQL statement, you need to double up on the single quotes, as in:

exec sp_MSforeachtable

@command1 = 'DELETE FROM ? ',

@whereand = '(TSTAMP < ''2007-01-01 00:00:00'')'

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Michael-760538 SSC Enthusiast Points: 155 More actions · Answer 14

exec sp_MSforeachtable

@command1 = 'DELETE FROM ? ',

@whereand = '(TSTAMP < ''2007-01-01 00:00:00'')'

Results the same:

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'TSTAMP'.