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