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

Berar

I don't think @@fetch_status is set until fetch is called, causing the while condition to not be true on the first pass. Is this an accurate copy of your procedure? – Nehru

@ShannonSeverance I have removed many cursors but this is the first one I ever wrote so I don't claim to be an expert but this does work as written. There is an odd behavior in that it can not be run twice; the second time it does nothing. To get around this I have to close the file and reopen it is SSMS, but that is outside the scope of my question. – Berar

I asked because it was not working for me. The error behavior is different then I expected. It doesn't work the second time because @@fetch_status is still -1 from the last fetch on that connection. That is the fetch that finished the prior run. See at msdn.microsoft.com/en-us/library/ms187308.aspx. To avoid a duplicate fetch I usually do declare ... open ... while (1=1) begin fetch ... if @@fetch_status<> 0 break /* else */ <do work> end close .... deallocate ... – Nehru

@ShannonSeverance Thanks, I whish I had know this when I was debugging this thing. I had to have closed and open the .sql file a dozen or more times. I added your suggested changes. The original code still runs (once) for me with no problems. I am using 2012 if that makes a difference. – Berar

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 

Nehru

After speed testing I did not end up using this approach but I marked this answer correct because it came closest to answering my original question of "Is there an alternative way of doing this without using a cursor and dynamic queries?" Thanks to everyone else who provided great insight into how much more I need to learn about SQL. – Berar

Answer 2

I don't think using using cursor and dynamic query here is a bad idea

One way is to append the delete queries and execute it at the end after generating all the delete queries.

Btw, cursor is just used for framing dynamic query so it is not a big deal

DECLARE @workingTable varchar(128);
DECLARE @sqlText nvarchar(max)='';
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) + ''';'
   
END
CLOSE curKey
DEALLOCATE curKey

--PRINT @sqlText
 EXEC (@sqlText)

Dihedral

Answer 3

You may get a bit better performance by doing the following:

DECLARE @workingTable SYSNAME;
DECLARE @sqlText nvarchar(MAX);
DECLARE @CheckDate DATETIME = DATEADD(YEAR, -2, GETDATE());

DECLARE curKey CURSOR LOCAL FAST_FORWARD 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.' + QUOTENAME(@workingTable) 
                  + ' WHERE LAST_MOD < @CheckDate'

     Exec sp_executesql @sqlText
                       ,N'@CheckDate DATETIME'
                       ,@CheckDate 
END
CLOSE curKey
DEALLOCATE curKey

Improvements:

1. Use appropriate data type for sql server object names tables (SYSNAME).
2. Use sp_executesql instead of EXEC(@Sql)
3. Pass the parameter as date, do not convert it to a string so that sql server can make use of indexes defined on that column.
4. Use QUOTENAME() function for put square brackets around the table names just in case any of the table name is a reserved key word in sql server, so the query wont error out.
5. Make your cursor local and fast_forward default settings for cursor are global , you don't need that right?

Lovesome

Thanks for the coding advice, I will certainly use it. The table names all harken back to FoxPro so none of them have spaces in them but QUOTENAME is a good trick to know, cleaner then my normal '...[' + @fieldNameVar + ']...' – Berar