T-sql to loop through all the tables in the database and delete records

Question

T-sql to loop through all the tables in the database and delete records

Dj463

Ten Centuries

Points: 1181
More actions
January 14, 2011 at 1:13 pm

#230402

Hello all,
I need a dynamic sql script to loop through all the tables in a database and delete records older than certain date.
Note: i dont want to use sp_msforeachtable stored procedure, i need the dynamic sql script

Viewing 6 posts - 1 through 5 (of 5 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Dj463 (1/14/2011)
Hello all,
I need a dynamic sql script to loop through all the tables in a database and delete records older than certain date.
Note: i dont want to use sp_msforeachtable stored procedure, i need the dynamic sql script

Not a difficult request for you to perform in your own right. Here's the steps:

1) Examine your database and confirm your inserted/updated date field is the same name in all tables. If not, correct this.

2) Generate a cursor off sys.tables for only user databases for their name.

3) Build out the SQL string, using the table variable from the cursor above and including it into the DELETE FROM @table WHERE Mycolumn <= @DateToDeleteUntil.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

Actually, it's not quite so easy to pull off in the presence of DRI. You'll need code to identify dependencies and that's not always the easiest thing to determine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Jeff Moden (1/14/2011)
Actually, it's not quite so easy to pull off in the presence of DRI. You'll need code to identify dependencies and that's not always the easiest thing to determine.

Sorry, DRI? I'm assuming you're talking about foreign key relationships and the like, where you'll have to deal with any children you'd need to delete if you haven't turned on cascades.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Dj463 Ten Centuries Points: 1181 More actions · Answer 4

Dj463

Ten Centuries

Points: 1181

January 14, 2011 at 8:00 pm

#1273868

could you please provide me with some of examples

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Dj463 (1/14/2011)
could you please provide me with some of examples

Show us your current work, and current code, and explain where you're having difficulty, and we'll see if we can't help you.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA