Check for Index on table

IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='Index_Name' 
    AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
  begin
    DROP INDEX [Index_Name] ON [SchmaName].[TableName];
  end

Perform Mass Table Drops

You can build up a string using the catalog views, e.g.:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DROP TABLE ' 
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id] 
    WHERE t.name LIKE 'LG_001%';

PRINT @sql;
-- EXEC sp_executesql @sql;

To just get the list of tables, use:

SELECT s.name, t.name 
  FROM sys.tables AS t 
  INNER JOIN sys.schemas AS s 
  ON t.[schema_id] = s.[schema_id] 
  WHERE t.name LIKE 'LG_001%';

The transaction log for database is full due to 'LOG_BACKUP'

There are only 2 ways the Transaction Log will truncate itself to reuse the internal space (Virtual log files) , 1 is through checkpoint process, on the simple recovery model when the log gets 70% full. The other, is after every Log Backup under Full recovery model. Maybe something is going on with these log backups. Perhaps they are completing in SQL Agent, but not doing anything at all? Try checking the backup history table in MSDB to make sure the log backups are happening and their physical file exist
SELECT TOP (10) a.database_name[DB],a.server_name[SQL INST],b.physical_device_name,a.backup_finish_date [BKP END DATE],CASE a.type
    WHEN 'D' THEN 'FULL'
    WHEN 'L' THEN 'LOG'
    END AS 'Backup Type'
FROM dbo.backupset a
JOIN dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE database_name IN ('Your Database Here') AND type = 'L'
ORDER BY backup_finish_date DESC
Thanks