Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Delete data from tables ending in _x

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
c0fee
Asked:
c0fee
2 Solutions
 
Alex MatzingerDatabase AdministratorCommented:
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
 
ragnarok89Commented:
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
 
gomiamCommented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
c0feeAuthor Commented:
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
 
c0feeAuthor Commented:
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
 
c0feeAuthor Commented:
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
 
Ryan McCauleyDatabase and Reporting ManagerCommented:
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
 
c0feeAuthor Commented:
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
 
Ryan McCauleyDatabase and Reporting ManagerCommented:
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
 
c0feeAuthor Commented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now