Pages

Thursday, April 5, 2018

Execute the result of a dynamic SQL using SP_EXECUTESQL

If the tables are dynamically backed-up in a trash database with 'xxx_test_table_YYYYMMDD' naming convention, following code can be used to dynamically delete historical backup tables, but keep the last 5.


--------------------------------------
-- Keep the last 5 backup tables and delete the rest for a given table in Trash DB
DECLARE @Source_Table VARCHAR(100)
DECLARE @Backup_Table VARCHAR(100)
DECLARE @Backup_Table_Prefix VARCHAR(100)
DECLARE @WhereClause VARCHAR(200) 
DECLARE @Date VARCHAR(8)
DECLARE @SQL1 NVARCHAR(MAX) ;
DECLARE @SQL3 NVARCHAR(MAX) = '' ;
--------------------------------------
BEGIN
 SET @Source_Table = 'Test_Table'
 SET @Backup_Table_Prefix = 'xxx_'
 SET @Date = CONVERT(VARCHAR(8),GETDATE(),112) -- YYYYMMDD
 SET @Backup_Table = @Backup_Table_Prefix + @Source_Table + '_' + @Date -- 'xxx_Test_table_YYYYMMDD'
 SET @WhereClause =  @Backup_Table_Prefix + @Source_Table + '_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
 --PRINT @WhereClause
 
 SET @SQL1 =  
  'SELECT @SQL2 += '' DROP TABLE trash.dbo.'''  + ' + a.name + '' ; ''' +
  ' FROM trash.sys.tables a
   JOIN (
    SELECT name,
    RankRows = ROW_NUMBER()over (ORDER by name DESC) 
    FROM trash.sys.tables 
    WHERE name like ''' + @WhereClause 
   + ''' ) b ON a.name = b.name WHERE b.RankRows > 5; '

 IF @SQL1 IS NOT NULL AND  @SQL1 <> ''
  --PRINT @SQL1; 
  EXEC SP_EXECUTESQL @SQL1 , N'@SQL2 NVARCHAR(MAX) OUTPUT', @SQL3 OUTPUT

 IF @SQL3 IS NOT NULL AND  @SQL3 <> ''
  --PRINT @SQL3; 
  EXEC SP_EXECUTESQL @SQL3
END
--------------------------------------

No comments:

Post a Comment