Solved

Delete data from tables ending in _x

Posted on 2011-03-11
10
296 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL query to generate xml 4 46
Text file into sql server 5 32
T-SQL Default value in Select? 5 37
TSQL convert date to string 4 34
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

809 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