Solved

Delete data from tables ending in _x

Posted on 2011-03-11
10
297 Views
Last Modified: 2012-05-11
I need to create a deletion script that will look for all tables ending in "_X" and delete data that is over 100 days old.  How would I go about doing that?

I know to find those tables, I can search the sys.Tables table, but am unable to go anywhere beyond that:
SELECT *
FROM sys.Tables
WHERE name like '%_X'

DELETE *
FROM <tables listed above>
WHERE date <= dateadd(d,-100, getdate())
0
Comment
Question by:c0fee
10 Comments
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35111566
you should be able to combine the queries like this:

DELETE * 
FROM (SELECT name FROM sys.Tables WHERE name like '%_X')
WHERE date <= dateadd(d,-100, getdate())

Open in new window


This will go through each table in that ends in _x and delete queries with a date over 100 days old
0
 
LVL 8

Expert Comment

by:ragnarok89
ID: 35111577
Sub del_x()

    Dim tDef As DAO.TableDef
    Dim db As DAO.Database
   
    Set db = CurrentDb
   
    For Each tDef In db.TableDefs
        If Right(tDef.Name, 2) = "_x" Then
            db.Execute ---your statement here---
        End If
    Next

End Sub
0
 

Expert Comment

by:gomiam
ID: 35111584
You may try to
SELECT 'DELETE FROM '|name|' WHERE date <= dateadd(d,-100, getdate());' FROM sys.Tables WHERE name like '%_X'

This is supposed to return a list of lines like this (I don't remember SQLServer's concatenation character, I typed | but it might be &):

DELETE FROM sometablename_X WHERE date <= dateadd(d,-100, getdate());

from which you could try to build a script.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 2

Author Comment

by:c0fee
ID: 35111600
I tried that nested query using a SELECT statement instead of a DELETE but received a syntax error:

SELECT *
FROM (SELECT name FROM sys.Tables WHERE name like '%_X')

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
0
 
LVL 2

Author Comment

by:c0fee
ID: 35111620
Ha. Everyone posted to this at the same time.  My previous post was to amatzinger.

Let me review gomiam's and ragnarok89's post...
0
 
LVL 2

Author Comment

by:c0fee
ID: 35111904
ragnarok89 - can you convert yours to a T-SQL script?
Gomiam - I can use your suggestion (the concatenation is a plus sign) to generate the DELETE statements for all the tables, but do not know how to wrap it inside a SQL script.  

I want to use this script to create a daily job...
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 500 total points
ID: 35111979
MSSQL doesn't allow table substitution directly, as the other answers seem to be showing - you'll get a syntax error. You can do this with a cursor, but I prefer a hidden sp called "sp_MSForEachTable" to do what you need:

exec sp_MSforeachtable
     @command1='DELETE [?] WHERE date < DATEADD(dd, -100, GETDATE)',
     @whereand='and object_id in (select object_id from sys.objects where name like ''%_X'')'

Open in new window


This will run this command for every table ending with "_X" as you want. As always, run it on a test database or inside a transaction first, to make sure it does what you expect!
0
 
LVL 2

Accepted Solution

by:
c0fee earned 0 total points
ID: 35112045
RyanM - that worked!  Nifty.  I just had to remove the brackets around the question mark and add () to the getdate function:

exec sp_MSforeachtable
     @command1='DELETE ? WHERE date < DATEADD(dd, -100, GETDATE())',
     @whereand='and object_id in (select object_id from sys.objects where name like ''%_X'')'

Thank you!
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35112168
Ah - I didn't check the syntax before I posted it, so sorry for the oversight. Glad it worked! That SP is undocumented, but I've used it tons of times. There's also another called sp_MsForEachDB that does the same thing for all the user databases on the server, in case you have some function you want run everywhere on the server.
0
 
LVL 2

Author Closing Comment

by:c0fee
ID: 35145562
Just a few changes to the syntax to make it work for me, but a very nice solution!
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question