SQL Server 2008 R2 – Script to Delete Tables with a Prefix

sql-server-2008-r2t-sql

I am running SQL Serve 2008 and we have a few test tables that were obviously used for testing. The prefix of the tables are Test_, my question is, will this script delete all tables with the prefix of Test_ on the database I execute it on?

declare @cmd varchar(4000)
declare cmds cursor for 
select 'drop table [' + Table_Name + ']'
from INFORMATION_SCHEMA.TABLES
where Table_Name like 'Test%'

open cmds
while 1=1
begin
  fetch cmds into @cmd
  if @@fetch_status != 0 break
  exec(@cmd)
end
close cmds;
deallocate cmds

Best Answer

will this script delete all tables with the prefix of Test_ on the database I execute it on?

Yes, it will DROP tables. DELETE is normally used with deletion of data. Your script will physically drop the tables.

Also, please dont use INFORMATION_SCHEMA and there is no need for a cursor. Your script can easily be written using dynamic sql.

EDIT : In comments, @MartinSmith pointed to one of his excellent answer - that I was not aware ... but is an eye opener when concatenating. (To be honest, I have never faced, but now will be aware of the gotcha ! Thanks again Martin !)

The ONLY guaranteed mechanism are the following:

  • Use cursor to loop through the rows in specific order and concatenate the values

  • Use for xml query with ORDER BY to generate the concatenated values

  • Use CLR aggregate (this will not work with ORDER BY clause)

DECLARE @sql nvarchar(max) = N'';

SELECT @sql = (select N'drop table ' + QUOTENAME(t.name) + N';'
FROM sys.tables AS t
WHERE t.name LIKE N'Test_%'
order by object_id 
 FOR XML PATH(''), TYPE
).value('.','nvarchar(max)')

print @sql ;
-- caution below will ACTUALLY drop the tables.
-- review the output and then uncomment below line
EXEC sys.sp_executesql @sql;
Related Question