SQL Sp or job to purge data

Are you looking for something like this? Assuming you have a column in your table that has a DATETIME in it, you can just use the DATEDIFF T-SQL function to delete rows older than some certain age.

DELETE YourTable
WHERE DATEDIFF(dd, RowDate, GETDATE()) > 10

If you want more granular timing, DATEDIFF supports a number of other denominations: http://msdn.microsoft.com/en-us/library/ms189794.aspx

I think your own the right track but I I don't want to delete the whole table just the rows older than 10 days.. does this do it?  Thanks

Yes - this query will do that. The WHERE clause filters out rows that are newer than 10 days old.

To confirm the number of rows that will be deleted, just modify the query slightly - replace the DELETE with "SELECT * FROM":

SELECT * FROM YourTable
WHERE DATEDIFF(dd, RowDate, GETDATE()) > 10

Select allOpen in new window

Scratch that.. I need to delete all rows in every table in the database. See screenshot attached.

I also need it to run every night without user intervention.

So you only need to delete rows older than 10 days from every table every night, or you need to delete all rows from every table every night?

Only row older than 10 days from every table every night.

Great.  Now if I do only want specific tables.. do I replace the ? with table names?  If there is more than one table how do I sperate them... with a semicolon?

This is what we have now but we want it simpler

/****************RUN NIGHTLY*********************/
DECLARE @DeleteDate datetime
 
SET @DeleteDate = DateAdd(day,-10,getdate())
 
SET NOCOUNT ON;
 
            DELETE FROM dbo.[A_DISPATCH_OUTCOME]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[A_MESSAGE_OUTCOME]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[ACTIVITY_LOG]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[ALERT]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[ALERT_STATUS_HISTORY]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[DISPATCH]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[DISPATCH_HISTORY]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[MESSAGE]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[MESSAGE_IMAGE]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[RECIPIENT]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[SCHEDULED_DISPATCH]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[SCHEDULED_HISTORY]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[SCHEDULED_RECIPIENT]
                  WHERE STORE_TIME < @DeleteDate
     
            DELETE FROM dbo.[ALERT_IMAGE_2010]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[ALERT_IMAGE_2011]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[ALERT_IMAGE_2012]
                  WHERE STORE_TIME < @DeleteDate
            DELETE FROM dbo.[ALERT_IMAGE_2013]
                  WHERE STORE_TIME < @DeleteDate
 
           
SET NOCOUNT OFF;
 
/****************END SCRIPT*******************/

Using the "sp_msforeachtable" means that it runs for every single table, no exceptions.

If you only want to run it on certain tables, then you've got to build a custom script, like the one you've already got.

ok, on every table then.  So now how do I ensure it runs every night.

You'll set up a SQL Agent job that runs every night - you can set the schedule - and then have it execute the script you've generated.

You can follow the steps in this article, except instead of the script where he runs "BACKUP DATABASE", use the script you've come up with to clear all your tables:

http://blogs.msdn.com/b/sqlagent/archive/2010/10/12/create-a-database-backup-job-using-sql-server-management-studio.aspx