在没有动态sql的情况下对多个表运行相同的查询

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.

我支持第三方软件包的SQL数据库。他们有很多所谓的“影子表”，就是审计表。这一切都很好，但他们的系统没有清理这些表，所以这取决于我。他们还添加了新的“阴影表”，没有注意每一次升级。我们清除表的旧方法是使用一长串从语句中删除的列表，但是这个列表已经变得非常长并且很难维护。

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

3 个解决方案

#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

如果您事先不知道表名，我不知道如何摆脱动态SQL。SQL Server有一个特性，可以在select语句中执行变量赋值，每一行返回一次。可以使用此方法消除游标，并将所有delete语句传递给SQL server以执行。

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 

#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.

一种方法是添加删除查询并在生成所有的删除查询之后在末尾执行它。

Between 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)

#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. 对sql server对象名称表(SYSNAME)使用适当的数据类型。
3. Use sp_executesql instead of EXEC(@Sql)
4. 使用sp_executesql而不是EXEC(@Sql)
5. 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.
6. 将参数作为日期传递，不要将其转换为字符串，以便sql server能够使用在该列上定义的索引。
7. 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.
8. 使用QUOTENAME()函数在表名周围放置方括号，以防任何表名是sql server中保留的关键字，因此查询不会出错。
9. Make your cursor local and fast_forward default settings for cursor are global , you don't need that right?
10. 使您的光标本地和fast_forward的默认设置是全局的，您不需要对吗?