针对没有动态SQL的多个表运行相同的查询 [英] Run the same query against multiple tables without dynamic sql
问题描述
我支持第三方软件包的SQL数据库。他们有很多所谓的影子表,实际上只是审计表。一切都很好,但是他们的系统不会清理这些表,所以我需要这样做。他们还将在每次升级时添加新的影子表,而无需另行通知。我们清除表的旧方法是使用很长的 DELETE FROM
语句列表,但是此列表已经很长且很难维护。
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
推荐答案
我不知道如果您不提前知道表名,无论如何都要远离动态SQL。 SQL Server具有一项功能,您可以在 select
语句中对返回的每一行进行一次变量分配。这可用于消除游标,并将带有所有 delete
语句的一个字符串传递给SQL Server以执行
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
这篇关于针对没有动态SQL的多个表运行相同的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!