?
Solved

Delete data from tables ending in _x

Posted on 2011-03-11
10
Medium Priority
?
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

762 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