Hello I did a Google search on this and found such a mixed bag of answers that I have no comfort around it.
This isn't something I have to get done it is more of a learning exercise. I have a strong NOC background and was recently recruited into a company for an all encompassing IT role.
My SQL skills are limited to a few queries I do for NOC troubleshooting so my overall SQL development skills are very weak. One of the developers posed a question to me because he wants me to "improve my SQL skills" it's not a mandatory work task. It's just something that if I get done, would really improve my ability with SQL.
The problem we are trying to solve is we want to delete records older than 4 years from a particular schema automatically without knowing the table names ahead of time. Basically if the table is in a particular schema a SQL server job will run a script that deletes records older than 4 year. Job will run once a day or once a week or whatever. The SQL script is what is important now I can easily figure out the job schedule.
Can you guys help me figure out how this would work? I was thinking the only way to do this would be dynamic sql (which I know nothing about, just that it might work) or maybe something like
select * from INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE LIKE '%date%'
DELETE FROM INFORMATION_SCHEMA.TABLES WHERE DATE < DATEADD(yy,-4,GETUTCDATE())
But I am complete SQL dev NOOB so not sure what I am getting.