Script to Delete Tables With A Prefix 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
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;