Solved

Delete data from tables ending in _x

Posted on 2011-03-11
10
298 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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

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.
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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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