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:
WHERE name like '%_X'
FROM <tables listed above>
WHERE date <= dateadd(d,-100, getdate())