I was in need of a utility script to drop a handful of tables that started with the same prefix the other night, and had to quickly write a script to do it. Since this is a utility script and not a stored procedure, I’m comfortable using EXEC without having nightmares about Little Bobby Tables.
The script starts by declaring a couple of variables to contain the SQL that is being built. The SELECT statement builds a list of tables that start with @Prefix, surrounded by brackets. The cool thing that might surprise you, is that DROP TABLE takes a comma separated list of tables.
DECLARE @TableList nvarchar(4000)
, @Prefix nvarchar(50) = 'dataogre%'
SELECT @TableList = COALESCE(@TableList + ', ', '')
+ QUOTENAME(SCHEMA_NAME(schema_id)) + '.'
WHERE name LIKE @Prefix
AND type = 'U'
--EXEC('DROP TABLE ' + @TableList)