So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows which will not be purged. Essentially, group the rows by #ofrowspurged and #ofrowsnotpurged.
Using this information is there any way to group the rows that have a date larger than 30 days, indicating how many rows will be purged and how many will not? To be more specific, I will be inputing the table name into the SQL query so you do not need to loop through multiple tables.
Let me know if you have any questions, as this explanation may be vague. Thanks!
Best How To :
The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have one called "UpdatedDate" that you need to check.
However, if ALL of the tables have the same date field you are comparing on, but only differ in table name, then you can dynamically check by doing the following:
declare @objcursor as cursor;
DECLARE @SQL nvarchar(max), @Schema sysname, @Table sysname;
SET @SQL = ''
SELECT @SQL = @SQL + 'SELECT '''+QUOTENAME(TABLE_SCHEMA)+'.'+
'= COUNT(*) FROM '+ QUOTENAME(TABLE_SCHEMA)+'.'
+QUOTENAME(TABLE_NAME) +' WHERE getDate() - [YOUR_COLUMN_NAME] >30;'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
PRINT @SQL -- test & debug
EXEC sp_executeSQL @SQL, -- getting variable input / setting variable output
,N'@cursor cursor output', -- params definition
@objcursor output -- output parameter
fetch next from @objcursor into @TableName,@Schema
while (@@fetch_status = 0)
/*Do Something With Your Table Name, eg delete, truncate, whatever*/
fetch next from @objcursor into @Table,@Schema