DROP Tables LIKE

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)) + '.' 
                       + QUOTENAME(OBJECT_NAME((object_id)))
 FROM     sys.objects
 WHERE    name LIKE @Prefix
          AND type = 'U'  
   
  PRINT @TableList
  --EXEC('DROP TABLE ' + @TableList)