Post reply

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

  • 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

  • January 14, 2011 at 1:54 pm

    #1273787

    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

  • January 14, 2011 at 2:52 pm

    #1273817

    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)
    Intro to Tally Tables and Functions

  • January 14, 2011 at 3:25 pm

    #1273833

    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

  • January 14, 2011 at 8:00 pm

    #1273868

    could you please provide me with some of examples

  • January 18, 2011 at 12:41 am

    #1274504

    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

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

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