Run the same query against multiple tables without dynamic sql
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)
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
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:
- Use appropriate data type for sql server object names tables (SYSNAME).
- Use
sp_executesql
instead ofEXEC(@Sql)
- 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.
- 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. - Make your cursor local and fast_forward default settings for cursor are global , you don't need that right?