Run the same query against multiple tables without dynamic sql

Question

I support a SQL database for a third party software package. They have a lot of what they call "Shadow Tables", really just audit tables. This is all fine and good but their system does not clean up these tables so it is up to me to do so. They also add new "Shadow Tables" without notice with every upgrade. The old way we were purging the tables was with a long list of DELETE FROM statements but this list has become very long and hard to maintain.

To try to make the purge process easier to maintain and automatically catch new "Shadow Tables" I wrote the following stored procedure. The stored procedure works but I would prefer to figure out a way without using a cursor and dynamic queries since this will be running daily on a lot of different tables. Is there an alternative way of doing this without using a cursor and dynamic queries?

DECLARE @workingTable varchar(128);
DECLARE @sqlText varchar(250);
DECLARE @CheckDate DATETIME = DATEADD(yy, -2, GETDATE());

DECLARE curKey SCROLL CURSOR FOR  
SELECT name AS TableName
FROM dataTEST.sys.tables
WHERE (name like '%[_]h' OR name like '%[_]dh')
ORDER BY name

OPEN curKey
WHILE @@fetch_status = 0
BEGIN
    FETCH NEXT FROM curKey INTO @workingTable
    SET @sqlText = 'DELETE FROM DataTEST.dbo.' + @workingTable + ' WHERE LAST_MOD < ''' + CONVERT(CHAR(10), @CheckDate, 101) + ''';'
    --PRINT @sqlText
    EXEC (@sqlText)
END
CLOSE curKey
DEALLOCATE curKey

Answer 1

I do not know of anyway to get away from dynamic SQL when you do not know the table names ahead of time. SQL Server has a feature where you can do variable assignment in a select statement, once for each row returned. This can be used to eliminate the cursor and pass one string with all the delete statements to SQL server to execute

DECLARE @sqlText nvarchar(MAX) = ''; -- initialize because NULL + 'x' is NULL
DECLARE @CheckDate DATETIME = DATEADD(YEAR, -2, GETDATE());

SELECT @sqlText = @SqlText + 'DELETE FROM dataTEST.dbo.' + QUOTENAME(name) 
    + ' WHERE LAST_MOD < @CheckDate ; '
FROM dataTEST.sys.tables
WHERE (name like '%[_]h' OR name like '%[_]dh')
ORDER BY name

IF @@ROWCOUNT > 0 
    EXEC sp_executesql @sqlText
        , N'@CheckDate DATETIME'
        , @CheckDate