CJSmith dot me

I dump stuff I find useful here

Tidy Sage Line 500 ERP Temporary Tables via TSQL

On

By

Chris Smith

In

,

The official Sage Help has a select statement to delete temporary tables, but doesn’t allow an automated way to remove.
This script will delete tables so you must take extra care, I recommend trialing this on your Development Sage ERP system first.
Review every step and when you are happy Commit the transactions (If run all in one go it will deliberatly Rollback)

Credits:
Chef Slagle
Sage Help

/*
This code is based on the Sage example to remove temporary tables.  Normally these are left when a binary has not exited in the normal way.
You must check to ensure the correct tables are selected as the query won't care if it picks up a user table.
Credits:
Chef Slagle - https://stackoverflow.com/questions/17901934/how-to-execute-sql-statements-saved-in-a-table-with-t-sql
Sage - https://ask.sage.co.uk/scripts/ask.cfg/php.exe/enduser/std_adp.php?p_faqid=25606&p_pv=1.790
*/
PRINT 'Warnings: THIS WILL DESTROY DATA, ENSURE NO USER DATA TABLES ARE IN THE FORMAT IN THE SELECT QUERY!!'
PRINT '--==YOU MUST ENSURE YOU HAVE A FULL BACKUP OF ALL TABLES AND YOUR RESTORE IS WORKING BEFORE RUNNING ANYTHING FROM THIS EXAMPLE!==--'
PRINT '--==RECOMMENDED TO BE DONE WHEN NO USERS ARE ON THE SYSTEM==--'
PRINT '--==TEST ALL SAGE MODULES AFTER THIS IS COMPLETE!!==--'
 
print 'Find out how many temp tables are on the system over 30 days old'
SELECT count(*) FROM sysobjects WHERE type = 'U' AND
(name LIKE 'pq[0-9]%t' OR name LIKE '[_]s[_][0-9a-z]%t' OR name LIKE '[_]p[_][a-z0-9]%t' OR name LIKE 'cbm[0-9]%t' OR name LIKE 'cbt[0-9]%t' OR name LIKE 'cibr[0-9]%t' OR
       name LIKE 'er[0-9]%t' OR name LIKE 'nla[0-9]%t' OR name LIKE 'nld[0-9]%t' OR name LIKE 'nle[0-9]%t' OR name LIKE 'nlh[0-9]%t' OR name LIKE 'nlr[0-9]%t' OR
       name LIKE 'nlsp[0-9]%t' OR name LIKE 'opi[0-9]%t' OR name LIKE 'plt[0-9]%t' OR name LIKE 'po[0-9]%at' OR name LIKE 'por[0-9]%t' OR name LIKE 'slt[0-9]%t' OR
       name LIKE 'tld[0-9]%t' OR name LIKE 'tlh[0-9]%t' OR name LIKE 'st[0-9]%t' OR name LIKE 'bu[0-9]%t' OR name LIKE 'cbx[0-9]%t' OR name LIKE 'mr[0-9]%t' OR
       name LIKE 'poi[0-9]%t')AND crdate < GETDATE() -30
 
print 'REVIEW THE TABLES WHICH WILL BE DELETED BY THE CODE BELOW!!  ENSURE NO USER TABLES PRESENT!!  DO NOT PROCEED IF USER TABLES PRESENT!!'
SELECT "name" FROM sysobjects WHERE type = 'U' AND
(name LIKE 'pq[0-9]%t' OR name LIKE '[_]s[_][0-9a-z]%t' OR name LIKE '[_]p[_][a-z0-9]%t' OR name LIKE 'cbm[0-9]%t' OR name LIKE 'cbt[0-9]%t' OR name LIKE 'cibr[0-9]%t' OR
       name LIKE 'er[0-9]%t' OR name LIKE 'nla[0-9]%t' OR name LIKE 'nld[0-9]%t' OR name LIKE 'nle[0-9]%t' OR name LIKE 'nlh[0-9]%t' OR name LIKE 'nlr[0-9]%t' OR
       name LIKE 'nlsp[0-9]%t' OR name LIKE 'opi[0-9]%t' OR name LIKE 'plt[0-9]%t' OR name LIKE 'po[0-9]%at' OR name LIKE 'por[0-9]%t' OR name LIKE 'slt[0-9]%t' OR
       name LIKE 'tld[0-9]%t' OR name LIKE 'tlh[0-9]%t' OR name LIKE 'st[0-9]%t' OR name LIKE 'bu[0-9]%t' OR name LIKE 'cbx[0-9]%t' OR name LIKE 'mr[0-9]%t' OR
       name LIKE 'poi[0-9]%t')AND crdate < GETDATE() -30
--Note the count above here and review the list of tables from the previous select statement.
print 'Run next step manually in BEGIN TRAN, job will Rollback just in case.  Only commit if number of tables deleted matches above!'
BEGIN TRAN
 
DECLARE @Table table (RID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
                        SQLText NVARCHAR(MAX) )
 
DECLARE  @StatementMax INT 
        ,@statementMin INT
        ,@isTest TINYINT = 1
        ,@SQLStatement NVARCHAR(MAX)
 
INSERT INTO @Table (SQLText)
(SELECT 'drop table scheme.' + "name" as SQLText FROM sysobjects WHERE type = 'U' AND
(name LIKE 'pq[0-9]%t' OR name LIKE '[_]s[_][0-9a-z]%t' OR name LIKE '[_]p[_][a-z0-9]%t' OR name LIKE 'cbm[0-9]%t' OR name LIKE 'cbt[0-9]%t' OR name LIKE 'cibr[0-9]%t' OR
       name LIKE 'er[0-9]%t' OR name LIKE 'nla[0-9]%t' OR name LIKE 'nld[0-9]%t' OR name LIKE 'nle[0-9]%t' OR name LIKE 'nlh[0-9]%t' OR name LIKE 'nlr[0-9]%t' OR
       name LIKE 'nlsp[0-9]%t' OR name LIKE 'opi[0-9]%t' OR name LIKE 'plt[0-9]%t' OR name LIKE 'po[0-9]%at' OR name LIKE 'por[0-9]%t' OR name LIKE 'slt[0-9]%t' OR
       name LIKE 'tld[0-9]%t' OR name LIKE 'tlh[0-9]%t' OR name LIKE 'st[0-9]%t' OR name LIKE 'bu[0-9]%t' OR name LIKE 'cbx[0-9]%t' OR name LIKE 'mr[0-9]%t' OR
       name LIKE 'poi[0-9]%t')AND crdate < GETDATE() -30)
--select * from @Table
SELECT @StatementMax = MAX(RID), @statementMin = MIN(RID)  FROM @Table
--IF @isTest = 1 BEGIN SELECT *, @StatementMax AS MaxVal, @statementMin AS MinVal FROM @Table END
-- Start the Loop
WHILE @StatementMax >= @statementMin
BEGIN
    SELECT @SQLStatement = SQLText FROM @Table WHERE RID = @statementMin        -- Get the SQL from the table 
    --IF @isTest = 1 BEGIN SELECT 'I am executing: ' + @SQLStatement AS theSqlBeingRun, GETDATE(), @statementMin, @StatementMax END  
    --ELSE 
    --BEGIN 
        EXECUTE sp_executesql @SQLStatement                 -- Execute the SQL 
    --END
              SELECT @statementMin = @statementMin + 1
        --DELETE FROM @Table WHERE RID = @statementMin        -- Delete the statement just run from the table
        --SELECT @statementMin = MIN(RID)  FROM @Table        -- Update to the next RID
    --IF @isTest = 1 BEGIN  SELECT * FROM @Table END
END
print 'Check number of executions matches expected value, check temp tables have been removed and if ok run COMMIT TRAN'
ROLLBACK TRAN
COMMIT TRAN

Categories

Archives