SELECT name FROM sys.tables WHERE name like 'Tabx%'
DECLARE @id varchar(255) -- used to store the table name to drop
DECLARE @dropCommand varchar(255) -- used to store the t-sql command to drop the table
DECLARE @namingPattern varchar(255) -- user to defie the naming pattern of the tables to drop
set @namingPattern = 'Tabx%'
DECLARE tableCursor CURSOR FOR
SELECT name FROM sys.tables WHERE name like @namingPattern
OPEN tableCursor
FETCH next FROM tableCursor INTO @id
WHILE @@fetch_status=0
BEGIN
-- Prepare the sql statement
SET @dropcommand = N'drop table ' + @id
-- print @dropCommand -- just a debug check
-- Execute the drop
EXECUTE(@dropcommand)
-- move to next record
FETCH next FROM tableCursor INTO @id
END
CLOSE tableCursor
DEALLOCATE tableCursor
if object_id('usr_keep_tables','U') is null create table usr_keep_tables (name sysname)
insert usr_keep_tables
Select name from sys.tables
-- you can see and if needed manually add or delete entries
select * from usr_keep_tables
and then once you have the "keep" list you can add a where clause or a join to automatically exclude the "keep" files from your cleanup
SELECT name
FROM sys.tables
WHERE name like @namingPattern
AND not exists (select null from usr_keep_tables K where k.name = sys.tables.name)
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented: