Purging Data from a SQL DB, Selectively.

I had this odd request to restore a database from backup, then clear all the data from the database with the excption of a dozen tables.  OK, I can do that but there is 1200 tables and I have other work to do too.  And you may notice from the tables it was the CM database.  So lets use a cursor against the list of tables and truncate some data!

USE CM_SMS

declare @name nvarchar(256)

declare cur CURSOR LOCAL for

SELECT name

FROM sys.Tables

--Keep data in these tables

where name not IN('Computer_System_Data','Network_Data','PC_Bios_Data','Operating_System_Data','Processor_Data','PC_Memory_Data','System_Enclosure_DATA','Disk_Data','Video_Controller_Data','WorkstationStatus_DATA','SYSTEM_CONSOLE_USER_DATA','Add_Remove_Programs_64_Data','Add_Remove_Programs_Data','SoftwareFile','System_DISC')

open cur

fetch next from cur into @name

while @@FETCH_STATUS = 0 BEGIN

    BEGIN TRY

     EXECUTE ('TRUNCATE TABLE ' + @name)

       print 'Success: ' + @name

    END TRY

     BEGIN CATCH

          print 'Failed: ' + @name

       END CATCH

    fetch next from cur into @name

END

close cur

deallocate cur