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