vendredi 20 septembre 2019

Determine if table can be truncated/deleted, run script, and loop back

I need to try to truncate tables and if they can GREAT do other stuff to the table but if they can't be truncated run through some script and then after that try truncate again and repeat. Something like this to start

    SELECT TABLE_NAME
    INTO #tbls
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' 
    AND TABLE_SCHEMA = 'dbo'
    --No temps or views
    AND TABLE_NAME NOT LIKE 'tmp%'
    AND TABLE_NAME NOT LIKE 'temp%'
    AND TABLE_NAME NOT LIKE 'vw%'

SELECT @tbl_cnt = COUNT(*)  FROM #tbls 

WHILE @tbl_cnt > 0 -- for each  table
BEGIN
SET @tbl_nm  = (SELECT top 1 TABLE_NAME FROM #tbls) 

select  @sql = 'TRUNCATE TABLE ' + @tbl_nm 

BEGIN TRY
    EXEC(@sql) --truncate works 
END TRY
BEGIN CATCH
--STUFF HERE TO DO WHEN TRUNCATE FAILS
END CATCH

/*LOOP BACK AND TRY TO TRUNCATE*/

/*more steps after successful truncate*/

@tbl_cnt = @tbl_cnt -1
DELETE FROM #tbls WHERE #tbls.TABLE_NAME = @tbl_nm
END

at the end it would be

WHILE @tbl_cnt > 0 
try to truncate
    if it works
    do more stuff
if it doesn't 
    do other stuff
try to truncate again
    Loop back to first truncate

try/catch might or might not be the way to go, tried other methods and that's the one I am working with now, just having a mental block with this. Any assistance appreciated.

Aucun commentaire:

Enregistrer un commentaire